Counting colored cells
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 |
Counting colored cells
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 |
Counting colored cells
Thank you very much,
Niall. |
Counting colored cells
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. |
Counting colored cells
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. |
Counting colored cells
Thanks for your help,
Much appreciated. Niall. |
Counting colored cells
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. |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com