Count conditional formats

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:

Code:

---

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.

:beerchug:

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

Code:

---

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.

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!

You may also try something like this ...

Code:

---

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.

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).

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.

Code:

---

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

---