SOLVED

Highlighted
New Contributor

Conditional Formatting values across tables

Hi,

Could someone please assist me with this. I have attached a photo to add context.

When I write values into the green table I would like the figures to become red if they are less then the corresponding cell in the yellow table. I know how to format each cells independently but is there a way to do it multiple times? It won't work if highlight multiple cells.

Cheers,

Chris

5 Replies
Highlighted

Re: Conditional Formatting values across tables

Hi,

you can do it column by column but you will still need to do the first cell manually

- use conditional formatting for the first cell  then  use Format Painter and extend to the end of the column.

unfortunately: the empty cells will considered have the value ( 0) so they will be painted in RED till you fill them with correct numbers

Highlighted
Solution

Re: Conditional Formatting values across tables

I'd suggest the rule with formula

`=(E3<A3)*NOT(ISBLANK(E3))`

applied to entire range. With that you don't format blank cells and do not generate a bunch of rules (one rule for each cell) as with Format painter.

Highlighted

Works Perfectly,

THANKYOU!

Highlighted

Re: Conditional Formatting values across tables

Hi,
what would the 2 formulas be, if the value was < A3 but only within 10% to equal yellow

and if it was greater then <10% = red
Highlighted

Re: Conditional Formatting values across tables

Hi,

When yellow

`=(E3<A3)*(E3>=0.9*A3)`

and red

`=(E3<0.9*A3)*NOT(ISBLANK(E3))`

if I understood you correctly