I got this code from a site on-line.

I need to scan a list of cells in a worksheet and search for any cells
that are colored red.

If any of the cells are colored red I need to display it in a separate
worksheet that deals with a summery of all the work so far. It would
be handy, although not essential if the number of red cells on a sheet
was given.

I think this code will do it apart from one problem. My current red
cells are red due to conditional formatting, I think that this is
causing problems.

Any ideas?

I am not too advanced with VBA so please be patient with me!!!

Thanks,

Niall.

|
|
|
v

code:


Function CountColour(Rng As Range, ColourMatch As Integer,
BackgroundOrFont As String)
'Rng is the set of cells to be checked
'ColurMatch is the Color INDEX of the colour being tested for
'BackgroundOrFont requires an "F" or "B" to indicate whether to test
Font or Background colour

Dim c As Range, TempStore As Long

TempStore = 0

Select Case BackgroundOrFont
Case "B"
For Each c In Rng
If c.Interior.ColorIndex = ColourMatch Then
TempStore = TempStore + 1
End If
Next
Case "F"
For Each c In Rng
If c.Font.ColorIndex = ColourMatch Then
TempStore = TempStore + 1
End If
Next
Case Else
CountColour = "Choose F or B only"
Exit Function

End Select

CountColour = TempStore

End Function