Function to count conditionally formatted coloured cells

Question

I found a macro which counts conditionally formatted coloured cells.

'Variable declaration
Dim lColorCounter2 As Long
Dim rngCell2 As Range
'loop throughout each cell in the range
For Each rngCell2 In Selection
    'Checking Amber color
    If Cells(rngCell2.Row, rngCell2.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
        lColorCounter2 = lColorCounter2 + 1
    End If
Next
MsgBox "Green =" & lColorCounter2

I am trying to change it into a function. I've read on here that it may be because .DisplayFormat.Interior.Color doesn't work for functions. Is there a workaround?

Ideally I would like the function to have two arguments, the range of cells to search in for the colours and the second a cell with the colour to look for.

The best advice I can give you is to rethink the entire approach. You'd be better off with formulas that test the conditions used for the conditional formatting. — Rory, Dec 27 '18 at 16:35
Take a look at: https://stackoverflow.com/questions/22366145/vba-function-to-test-if-cell-is-conditionally-formatted-in-excel . Chris' answer is pretty solid response to what can work, though the comments to the main post hold what i feel is the mos tvaluable info. — Cyril, Dec 27 '18 at 16:41
Or this one: https://stackoverflow.com/questions/16330345/vba-to-identify-cells-in-red — VBA Pete, Dec 27 '18 at 16:42
Why are you writing it like `Cells(rngCell2.Row, rngCell2.Column)`? Just do `rngCell2.DisplayFormat...`, etc. — dwirony, Dec 27 '18 at 17:04

score 0 · Answer 1 · answered Dec 28 '18 at 08:36

Have in mind that:

RGB(255, 192, 0) is not green but close to orange.
Change the range you want to loop - rng (now rng equals to Sheet1.Range("A1:A20"))

Try:

Option Explicit

Public Function Color(ByVal rng As Range)

    Dim Counter As Long
    Dim Cell As Range

    For Each Cell In rng
        'Checking Amber color
        If Cells(Cell.Row, Cell.Column).DisplayFormat.Interior.Color = RGB(255, 192, 0) Then
            Counter = Counter + 1
        End If
    Next

    MsgBox "Orange=" & Counter

End Function

Sub test()

    Dim rng As Range

    Set rng = Sheet1.Range("A1:A20")

    Call Color(rng)

End Sub

Thanks for your help everyone but unfortunately these solutions won't work for me. I have around 25 different arrays of cells with many different conditional formatting conditions applied that I need to count the resulting colours of every month, so this would only really work as a function where I can input the respective cell array — Dan, Jan 16 '19 at 16:32

Function to count conditionally formatted coloured cells

1 Answers1