Excel Conditional Formatting Cells in a row

Question

I have an excel spreadsheet organizing material orders, I have 2 columns "Date Expected" and "Date Delivered" I have 1 row functioning the way that I want it to which is if "Date Expected" is past due and "Date Expected" is blank, then the 2 columns in that row will be red.

The problem is, I want the entire sheet to function this way without having to manually enter the rules for each row. I think it has something to do with the "$" but I can't get it to work.

This is what I have...

Formula 1: =IF(ISBLANK($G$2),ISBLANK($H$2)) Applies to: =$G$2:$H$2 Formula 2: =IF(ISBLANK($H$2),$G$2<TODAY()) Applies to: =$G$2:$H$2

This DOES work the way I want it to, but I would have to format each row separately. I am very new to excel, please help!

Answer 1

This is what you need to do using Coditional Formatting:

---

• Firstly, select the range, here as in example shown: $G$2:$H$10 (you may need to extend as per your suit)
• From Home Tab --> Under Styles Group --> Click Conditional Formatting --> Click New (Alternatively Shortcut to open in Windows --> ALT+H+L+N)
• On Selection above, opens a New Formatting Rule window
• Select the last rule --> Use a formula to determine which cells to format
• Enter the following formula in the edit the rule description

---

=AND($G2<TODAY(),$G2<>"",$H2="")

---

• Click Format --> Choose preferred formatting for the rule.
• Click Ok twice done!

---

To make shorter we can use the * operator instead of AND() which does the same logic.

=($G2<TODAY())*($G2<>"")*($H2="")

---