# Cell background colours

#### RichardMGreen

##### Well-known Member
Hi all

Is it possible to count the number of times a background colour appears using a simple(ish) function rather than VBA?

I want to write a formula that will give an overall reading (red/amber/green) depending on the colours of 6 other cells. The other cells are driven by a base colour and then changed by conditional formatting.

Anyone any ideas?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Richard Schollar

##### MrExcel MVP
Richard

If the colours are the result of conditional formatting then you already have the criteria required to perform the count (ie it is the same criteria used in the Conditional Formatting). Thus some form of Sumproduct would likely work.

#### Thorin

##### Board Regular
Richard,

Can you not re-create your conditions from the conditional formatting in your formula ?

Any chance of posting what your conditional formatting rules are ?

#### RichardMGreen

##### Well-known Member
Thanks folks - should have spotted the obvious!

#### RichardMGreen

##### Well-known Member
Hia ll

I'm trying to use this function to work out what colour the cell in question should be using conditional formatting.
Code:
``````Function cellcolour(p1 As Range, p2 As Range, p3 As Range, s1 As Range, s2 As Range, s3 As Range)
If p1.Interior.ColorIndex = 12 Or p2.Interior.ColorIndex = 12 Or p3.Interior.ColorIndex = 12 Then cellcolour = 2
If (p1.Interior.ColorIndex = 12 And p2.Interior.ColorIndex = 12) Or (p1.Interior.ColorIndex = 12 And p3.Interior.ColorIndex = 12) Or (p2.Interior.ColorIndex = 12 And p3.Interior.ColorIndex = 12) Then cellcolour = 1
End Function``````

At the minute, I only have information in cells P1,P2 and P3 so they are changing colour.
P1 and P3 are the same red but are coming up with different colourindex figures (even though it uses the same conditional formatting for the changing of the colours).

Anyone know how to sort this out?

::edit::
Please ignore this - light has finally dawned I can use the values that are driving the conditional formatting.

#### Richard Schollar

##### MrExcel MVP
Richard

Conditional Formatting doesn't change the cell's Interior.ColorIndex setting.

Replies
1
Views
469
Replies
10
Views
627
Replies
2
Views
564
Replies
4
Views
373
Replies
11
Views
454

1,190,774
Messages
5,982,842
Members
439,799
Latest member
matts12

### 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?

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