Conditional Formatting Counting Colours

Joined
Jan 25, 2005
Messages
1
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ealingwill

New Member
Joined
Jan 27, 2005
Messages
10
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
 

Forum statistics

Threads
1,147,507
Messages
5,741,566
Members
423,667
Latest member
Kai_357

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top