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

See http://www.xldynamic.com/source/xld.CFConditions.html

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Niall 84" wrote in message
...
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

Thank you very much,

Niall.

Don't know if this will help, but I set up a range from A1 to C12 and
put some conditional formatted cells in the range for red interior.
This macro counted them accurately.

Sub countColor()
Dim c As Range
Set myRange = Sheets(1).Range("A1:C12")
cntr = 0
For Each c In myRange
If c.FormatConditions.Count 0 Then
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
cntr = cntr + 1
End If
End If
Next
MsgBox cntr
End Sub


"Niall 84" wrote:

Thank you very much,

Niall.

One problem with counting conditional format colored cells is that the cell
might get counted even if the criteria is not met to set the color. The code
counts the FormatCoditions with a Interior.ColorIndex = 3, not the actual
interior color.
In other words, it does not consider if the criteria is met that would
actually color the interior.



"Niall 84" wrote:

Thank you very much,

Niall.

Thanks for your help,

Much appreciated.

Niall.

See the link I provided for a way to handle this.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JLGWhiz" wrote in message
...
One problem with counting conditional format colored cells is that the
cell
might get counted even if the criteria is not met to set the color. The
code
counts the FormatCoditions with a Interior.ColorIndex = 3, not the actual
interior color.
In other words, it does not consider if the criteria is met that would
actually color the interior.



"Niall 84" wrote:

Thank you very much,

Niall.