Conditional formating-change color of cells over than one with for

[carrera]

Let's say in D55 on each sheet is a formula showing a cumulative total from
the prior sheets

If the cumulative total in D55 is less than 40, I'd like cells in rows 1
through 49 to be green, if over 40, turn the cells red.

In other words, can I make the condition formating dependant on a totally
different cell?

 

[Roger Govier]

Hi

Mark the range of cells in rows 1 to 49 that you wish to format.
Format>Conditional Formatting> use dropdown for Formula Is>
=$D$55<=40 Set Format Green
Similarly =$D$55>40 Set Format to Red

 

[Bob Phillips]

Select rows 1 through 49

In CF, change condition 1 to Formula Is
Add a formula of =$D$55<40
Format as required
Add condition
Add a formula of =$D$55>4
Format as required
OK

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Alan]

Yes you can, in all the cells you want to conditionally format use the
'Formula' option and enter =$D$55<40 and format as you require, then you
have two options. If you want the cells to be of no colour if they are
empty, use 'Add' and in the second condition use 'Formula' again and enter
=$D$55>40, formatting again as necessary. If there will always be a value of
some kind in them, just format them red or green as you wish in the usual
way and the conditional formatting will change them if the condition is met.
If you choose the first option you need to decide what colour you want if
the value is exactly 40 by using =$D$55>=40 or whatever you choose,
Regards,
Alan.

 

[Matt]

Let's say in D55 on each sheet is a formula showing a cumulative total from
the prior sheets

If the cumulative total in D55 is less than 40, I'd like cells in rows 1
through 49 to be green, if over 40, turn the cells red.

In other words, can I make the condition formating dependant on a totally
different cell?