Count conditional formats

[Ken Puls]

Hi there,

Anyone know of an easy way to count the number of cells with a conditional format that has been applied?

I have a range of cells which turn yellow if they hold suspect data. I want to warn the user to review it before they pull the trigger on another macro...

I tried this:

Sub test()
Dim cl As Range, clcount As Long
For Each cl In Selection
    If cl.Interior.ColorIndex = 6 Then
        clcount = clcount + 1
    End If
Next cl
    MsgBox clcount
End Sub

No hits, though. I'm sure I can find something, but am just wondering if someone else has already been there and can save me some time.

[Jacob Hilderbrand]

What you need to do is check for the condition and not the resulting format.

Sub test() 
    Dim cl As Range, clcount As Long 
    For Each cl In Selection 
        If cl.Value = 10 Then 
            clcount = clcount + 1 
        End If 
    Next cl 
MsgBox clcount 
End Sub

Assuming that a Value of 10 triggers the Conditional Format.

[Ken Puls]

Hmmm...

Odd that there would be no Boolean property to tell if a FormatCondition object had been triggered, but oh well.

I actually have three conditions set up: The first to leave it unformatted, the second two turn the cell yellow if invalid data is submitted. (I would noramlly use Data Validation, but I want the data entry to be quick and message free at this point.)

I guess that I'm either stuck with checking all cells for all three conditions at the end, or using a ws_change event to set the interior.colorindex on the cell at time of entry, then looping as I first tried.

Thanks for the info, Jake!

[Zack Barresse]

You may also try something like this ...

Option Explicit

Sub CountCF()
    Dim rngF As Range, rngC As Range, cnt As Long
    Dim firstAddy As String, cel As Range
    Set rngF = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
    Set rngC = rngF.Find(10, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
    If Not rngC Is Nothing Then
        firstAddy = rngC.Address
        Do
            Set rngC = rngF.FindNext(rngC)
            cnt = cnt + 1
        Loop Until rngC.Address = firstAddy
    End If
    MsgBox cnt
End Sub

Not sure how much faster it would be, but it wouldn't be looping through every cell.

[Ken Puls]

Uhhh....

Okay, you lost me. I understand the point, but this just returns me a 0 every time.

Here's the deal... Data will be in range("A3:G" & Range("A65536").end(xlup).row)

All I really want to know is if there is 1 cell with a colorindex of 6 (set through the contional format).

[Jacob Hilderbrand]

Zack's suggestion will search the cells which is much faster then a loop. If you only want to know if there is one or more then we don't need a loop.

Change rngF to your range and get rid of the Loop.

If Not rngC Is Nothing Then 
'We have an error
Else
'No error
End Sub