Excel -Applying Formula based on Cell Colour

[RedMoo]

Hi,
I am trying to apply a couple of formulas on a row based on the Cell Colour.
So lets say in this row there are green, red and blue cells. I am trying to create 3 formulas, one to add the values in the red, one for the green and one for the blue cells.

Any help would be appreciated.

 

[SkipVought]

RedMoo,

Can't do that with native worksheet functions. You'll need VBA code, unless there is some logic to the colors based on the data in the rows, in which case, you could use Format/Conditional Formatting to perform the shading and could use the same criteria for this excersize.

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[KenWright]

You cannot do this using formulas without resorting to UDFs, UNLESS, the cell colouring is based on conditional formatting, which themselves will be based on logic that can be included within a formula - Yuk - Sounds horrible so example:-

Assume that you have 10 cells (A1:A10), and each is coloured 1 of 3 colours based on the following:-

Cell value is 1-2 then red
Cell value is 3-6 then green
Cell value is 7-9 then blue

What you can then do is use the same logic that colours your cells, but within a formula, so if for example you wanted to count all the cells that were green, then that is the same as counting all cells with a value between 3 and 6, and so

=COUNTIF(A1:A10,">=3")-COUNTIF(A1:A10,">6")

Same principle can often be applied to any colouring where logic has dictated that colouring.

Regards
Ken...........


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

LOL - Hey Skip, Scary or what eh

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

do[sup]do[/sup]do[sub]do[/sub]do[sup]do[/sup]do[sub]do[/sub]

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[RedMoo]

Unfortunately there is no logic in applying the colours. The colouring is representative and not based on the numbers that will be populating the cells.
It is almost like Conditional Formating in reverse really.

Thanks for your help guys.
I will just have to go a different route.

 

[KenWright]

No problem, but it can be done, but as we said, it means using VBA so not as straightforward as just using formulas.

Here is a link to Chip Pearson's site where he gives you examples

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

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------