Using a conditional coloring *set* depending on value of a cell

[Bus]

This is an outstanding site for Excel.


I had a conditional formatting question for a dashboard I am developing. I would like to use different conditional formatting *sets* depending on value of a different cell.


For e.g.,


If J1=1, then if C2>98 format cell C2 green, if C2<98 format cell C2 amber

If J1=2, then if C2>95 format cell C2 green, if C2<95 format cell C2 amber

etc.


Many thanks in advance,

 

[vasim]

http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

 

[Bus]

Thanks, but that's not it.


I am using a different conditional formatting color set/pallete depending on value of a different cell.

 

[Hui]

Bus


You will need to add 4 Conditional formats like the below to cell C2


=AND($J$1=1,$C$2>98) Set Green

=AND($J$1=1,$C$2<98) Set Amber

=AND($J$1=2,$C$2>95) Set Green

=AND($J$1=2,$C$2<95) Set Amber

 

[Bus]

Thank you Hui, for the simple and elegant solution. Much appreciate it.

 

[Luke M]

For any 2003 users out there:

=OR(AND($J$1=1,$C$2>98),AND($J$1=2,$C$2>95)) Set Green

=OR(AND($J$1=1,$C$2<98),AND($J$1=2,$C$2<95)) Set Amber