You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
Question marked as Top-ranking reply

Posted on Jul 12, 2021 6:01 PM

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


  1. Add another column. In my example below it is column J.
  2. The formula in this column is =IF(I>0,"",TODAY())
  3. Select all the cells in column H that need the highlighting formula, starting at H2
  4. 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.
  5. Hide the new column when it is all set up.


Similar questions

2 replies
Question marked as Top-ranking reply

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

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


  1. Add another column. In my example below it is column J.
  2. The formula in this column is =IF(I>0,"",TODAY())
  3. Select all the cells in column H that need the highlighting formula, starting at H2
  4. 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.
  5. Hide the new column when it is all set up.


Conditional Highlighting across Cells

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.