conditional formatting referring to another cell

[Johnexcel]

Hi all,

I currently have one cell that has a format in which it's color turns red, yellow, or green based on whether the % of the budget remaining is below 33% of the original budget (red), between 33% and 66% (yellow), and above 66% (green). This is to easily point out whether that particular budget is running dry.

This cell works fine, but i want to apply the same color format to the company name, which is in a separate cell. I want the cell containing the company name to turn one of the 3 colors based on that original cell i pointed out earlier. Can this be done? It seems to work for green and red by using a formula as the reference, but not when i try to use =$C$22<$F$12<$C$21

C22 is 34%, F12 is the calculated % remaining out of the original budget, and C21 is 66%

 

[Norie]

Try using AND.

=AND($F$12>=$C$22, $F$12<=$C$21)

By the way you could actually do this without a formula by using cell references when you choose 'cell value' and between.

You would normally use a formula if you wanted to apply the formatting to more than one cell.

For example if you select F10:F30 and enter this formula:

=AND($F10>=$C$22, $F10<=$C$21)

That should appy the formatting to those cells.

You could also take things further by selecting whole rows or multiple columns if you want.