CODEWITHSUNDEEP
excelformattingformat
Colin Burt
Colin Burt

Reputation: 13

Conditional Formatting Excel Rows Based On Relationship To TODAY()

I am trying to use 3 rules to format the row 3 separate colours based on the information in various cells.

Example of table

I am trying to get the whole row to operate a traffic light system: Amber if there is a date in Date Rcvd Red if either Due Date is before Today Green if Invoice Date is not blank.

the amber and green rules appear to work using the following:

=NOT(ISBLANK(A2)) to turn row amber, =IF(D2,TRUE) to turn row green but =OR($B2 < TODAY(),$C2 < TODAY()) does not turn row red if due dates are missed, I have tried various And/IFs etc but I cannot get this to work.

Your help is greatly appreciated as I'd love to get this working to impress in new job

Upvotes: 1

Views: 95

Answers (1)

pnuts
pnuts

Reputation: 59475

the amber and green rules appear to work

so just considering

Red if either Due Date is before Today

Please select ColumnsA:D and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(ROW()<>1,$B1<>"",MAX($B1,$C1)<TODAY())

Format..., select red colour Fill, OK, OK.

As mentioned by @Scott Craner, you may have to decide on the priorities for the three colours and order accordingly, perhaps applying Stop if True to suit.

Upvotes: 1

Related Questions