Cell background colours

[RichardMGreen]

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?

 

[Richard Schollar]

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.

 

[texasalynn]

take a look at Chip Pearson's information about color's
http://www.cpearson.com/excel/colors.htm

 

[Thorin]

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]

Thanks folks - should have spotted the obvious!

 

[ADAMC]

Can be done with a UDF, see this post:

http://www.ozgrid.com/VBA/Sum.htm

 

[RichardMGreen]

Hia ll

I'm trying to use this function to work out what colour the cell in question should be using conditional formatting.

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]

Richard

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