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.

:beerchug:

[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