VBA macro or function to count the number of conditionally formatted colored cells

[bearcub]

I have a huge spreadsheet that contains patient visit dates spanning three years. The visits can range from weekly to monthly to quarterly. I have over two patients that start at different times during this time and I've used conditional formatting to highlight those visits who visit dates fall within a particular date range (the cells turn pink if they fall within this date range, for example). I can spend hours counting and doubling counting the highlighted dates to ensure that I haven't missed anything.

Is there a VBA macro or function that will look at the conditionally formatting color I'm using and count the number of times that this color appears in the range of cells (the ones that are pink- the conditional formatting color I'm using)?

For example, if I have a range going from A1:AZ5000 I might have several hundred patients that have visit dates that fall within a particular range and are highlighted pink as a result. Is there a way of doing this in VBA?

Thank you for your help.

Michael

 

[baitmaster]

It's not easy to read conditional formats, but those formats are based on formulas, so you need to use formulas to give your answer

So you want to count the number of individual patients (in say column A) that have one or more dates (in say columns C:Z) that fall within a specific range? Therefore I'm guessing your CF is returning a True/False formula which you could break out into an additional column B, then use that column for both your CF and the result you're looking for

If your data is structured differently and there are issues with counting each person only once, then let us know. Please describe your data layout better, and someone will probably come up with a formula that takes account of this

 

[RCBricker]

I have a huge spreadsheet that contains patient visit dates spanning three years. The visits can range from weekly to monthly to quarterly. I have over two patients that start at different times during this time and I've used conditional formatting to highlight those visits who visit dates fall within a particular date range (the cells turn pink if they fall within this date range, for example). I can spend hours counting and doubling counting the highlighted dates to ensure that I haven't missed anything.

Is there a VBA macro or function that will look at the conditionally formatting color I'm using and count the number of times that this color appears in the range of cells (the ones that are pink- the conditional formatting color I'm using)?

For example, if I have a range going from A1:AZ5000 I might have several hundred patients that have visit dates that fall within a particular range and are highlighted pink as a result. Is there a way of doing this in VBA?

Thank you for your help.

Michael

you didn't state where you wanted the information to appear so I put it in a msgbox.

Also, to get the correct color for the cell.interior.color= line just record a macro and choose the color of pink you are using. then step into that macro and copy the line and paste it into the code instead of the color pink I used.

Sub bearcub()

Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim lngrow As Long, lngcol As Long
Dim i As Variant
    Set ws = Sheets("bearcub") 'Change the name of this to your sheet name
    With ws
        lngrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        lngcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lngrow, lngcol))
        i = 0
        For Each cell In rng
            If cell.Interior.Color = 10053375 Then
                i = i + 1
            End If
        Next cell
    End With
    
    MsgBox = i
End Sub

 

[baitmaster]

@Bearcub - we're looking for a conditional format colour, not the interior colour. See Chip Pearson's page for detailed info on the subject Conditional Formatting Colors

 

[MARK858]

Adaption of the code buy RCBricker to allow for conditional formatting as Range.interior.Color doesn't work for conditional formatting. Please note that Display.Format only came in with 2010 (I think))

Sub bearcub()

Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim lngrow As Long, lngcol As Long
Dim i As Variant
    Set ws = Sheets("bearcub") 'Change the name of this to your sheet name
    With ws
        lngrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        lngcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lngrow, lngcol))
        i = 0
        For Each cell In rng
            If cell.DisplayFormat.Interior.Color = 10053375 Then
                i = i + 1
            End If
        Next cell
    End With
    
    MsgBox i
End Sub

 

[baitmaster]

"displayformat, came in in 2010..."

That's why I love this board, still learn new stuff... just wish I'd known this one earlier, so much for Chip Pearson, harrumph!

 

[MARK858]

I still prefer to count conditional formatting based on a count of the format condition (i.e. via formula as previously mentioned) but then I am old fashioned (and IMO what Chip wrote was good for the time, it is just things change and move on).

 

[bearcub]

Thank you all for your help. I will try these when I get to work. We are using Excel 2013

However, I apologize for not specifying that I would like to use a function where I could point to the cell with the color and the count would appear in the cell. Thank you for the tip from Chip Pearson, I will check that out too.

I normally use the pink conditional formatting under the highlight cell rules in conditional formatting.

 

[Rick Rothstein]

"displayformat, came in in 2010..."

That's why I love this board, still learn new stuff... just wish I'd known this one earlier, so much for Chip Pearson, harrumph!

Note that DisplayFormat counts both interior colors and conditional format colors without distinguishing which is which, so if you have the same color scattered around in both interior and conditional formats, and if you want to count only the conditional formats, you will need to maintain two counts while looping your cells, one for the DisplayFormat and another for the Interior of that color, and then subtract the Interior count from the DisplayFormat count.