Finding % based on conditional format dates

[gulonraid]

Good morning,

I am building a training tracker for work. I figured out how to use the conditional formatting to highlight when training will be due based on expiration dates. What I would like to do it have it show a % next to a persons name based on their row of training. So if a CBT date goes red, it would lower the %. I have attached the excel doc to show what it currently looks like.

 

[Joe4]

No, this code updates column B as data is updated.
So it is designed to work as data is being entered.
If you want it do run against all pre-populated data, you would need another Macro to do that, that is very similar.

Here is the version that you can manually run on existing data:

Sub InitialRun()

    Dim sc As Long
    Dim ec As Long
    Dim ccode As Double
    Dim lr As Long
    Dim rng As Range
    Dim r As Long
    Dim c As Long
    Dim ctr As Long

'***Specify starting and ending column numbers in watched range
    sc = 4  '(column D)
    ec = 15 '(column O)
    
'***Specify color code
    ccode = 10285055
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting in row 5
    For r = 5 To lr
'   Loop through columns to get count
        For c = sc To ec
'           Add to counter if cell matches color code
            If Cells(r, c).DisplayFormat.Interior.Color = ccode Then
                ctr = ctr + 1
            End If
        Next c
'       Update column B with percentage
        Cells(r, "B").Value = ctr / (ec - sc + 1)
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

So, if you have existing data, but you may also be making changes to it, and you want the calculation to update dynamically, you would use both codes.

 

[gulonraid]

No, this code updates column B as data is updated.
So it is designed to work as data is being entered.
If you want it do run against all pre-populated data, you would need another Macro to do that, that is very similar.

Here is the version that you can manually run on existing data:

Sub InitialRun()

    Dim sc As Long
    Dim ec As Long
    Dim ccode As Double
    Dim lr As Long
    Dim rng As Range
    Dim r As Long
    Dim c As Long
    Dim ctr As Long

'***Specify starting and ending column numbers in watched range
    sc = 4  '(column D)
    ec = 15 '(column O)
   
'***Specify color code
    ccode = 10285055
   
    Application.ScreenUpdating = False
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows starting in row 5
    For r = 5 To lr
'   Loop through columns to get count
        For c = sc To ec
'           Add to counter if cell matches color code
            If Cells(r, c).DisplayFormat.Interior.Color = ccode Then
                ctr = ctr + 1
            End If
        Next c
'       Update column B with percentage
        Cells(r, "B").Value = ctr / (ec - sc + 1)
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

So, if you have existing data, but you may also be making changes to it, and you want the calculation to update dynamically, you would use both codes.

Awesome! Thank you so much it worked like a charm!!

 

[gulonraid]

Joe4, I had one more favor since you were so helpful. How would I change/add an extra VBA code if I wanted to do %s for each individual training column. I have attached a picture as the reference.
I would like to do it so it tracks all the green in that specific column and pulls a % for that based on current dates and future dates like it does on each side.