Conditional Formatting Counting Colours

[davidmcnaughton_1999]

I currently have a spreadsheet that has conditional formatting applied to its
rows. This conidtional formatting works by looking at the values in cells.

I would like to be able to count the colours within the conditional
formatting, ie

Red = 100
Orange = 50
Green = 360

I cannot use countif becuase the conidtional formatting (Format->Conditional
Formatting) uses a complex formulas, how can i do this using VBA.

Thanks

 

[ealingwill]

David,

Had exactly the same problem myself yesterday.

What i did was write a simple function that returns the colour as a text string. You can change this to whatever you want, i.e. number etc. But have put the basic code below. You then use this by putting =whatcol(A1) to find the colour of A1. You will obviously have to edit this to reflect the colours you are using. An easy way to find out the colorindex (e.g. 35 for pastel green) is to record a macro where you change the cell colour to the one you're looking for and VB handily records the colorindex number for you.

Function whatcol(ws As Range) As String

If ws.Interior.ColorIndex = 35 Then
whatcol = "Green"
ElseIf ws.Interior.ColorIndex = 34 Then
whatcol = "Blue"
ElseIf ws.Interior.ColorIndex = 38 Then
whatcol = "Pink"
End If

End Function

What i am trying to do however is, like yourself, count the number of each colour that are returned in a range. Any idea how you might be able to put a function within the countif?

Will

 

[ealingwill]

David,

Also found this posted by firefytr on another thread.

Hi there, welcome to the board!!


Here is a link that has an example and a downloadable example spreadsheet, along with directions ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=89

Chip Pearson also has an excellent solution here ...

http://www.cpearson.com/excel/colors.htm

Good luck

Will