Conditional Highlighting across Cells

Question

Level 1

11 points

Conditional Highlighting across Cells

Hi there - thanks for reading.

In the screenshot below it shows in Column H conditional Highlighting RED if the date is in the past and PEACHY if it's in the future (ie. is the invoice overdue or not).

In Column I the fill is GREEN if the value is above zero (ie. to show the invoice as paid).

However, if it's been paid, I no longer want the red date in Column H.

I'd like to be able to type somewhere (where?)..:

IF (I4>0) THEN H4 'Fill a lovely green'

Can it be done?

Excited to find out.

Thanks again,

David

Mac mini, macOS 10.15

Posted on Jul 12, 2021 4:44 AM

Reply

Answer 1

Badunit

Level 7

30,336 points

Jul 12, 2021 6:01 PM in response to dsh77

There's probably other ways to do it but here is one.

Add another column. In my example below it is column J.
The formula in this column is =IF(I>0,"",TODAY())
Select all the cells in column H that need the highlighting formula, starting at H2
Create the rule shown in the screenshot below. It will adjust the row number for all the other rows that get the rule. I used yellow instead of "peachy" for "in the future". I used orange for "the present" because you did not specify what color to use then. You can change that to "peachy" or red or delete it entirely if you want it not highlighted.
Hide the new column when it is all set up.

Reply

Answer 2

dsh77 Author

Level 1

11 points

Jul 14, 2021 3:57 AM in response to Badunit

Thank you very much. The formula for the J column was beyond my reach. Much appreciated.

Reply

Conditional Highlighting across Cells

Similar questions