Need help with Highlight with Conditional Formatting

Copper Contributor

Using Excel 365. I have this data pulling from a query. This data will change daily. It's giving me a count of certain data based on the date of entry. I need it to highlight based one of two conditions. One of the conditions I have figured out.


1st Condition: If the number on any date of the month is greater than 1, it needs to highlight that row. I've got this figured out. Unfortunately, I have to create a rule for each day to cover each column separately I believe. Correct me if I'm wrong, but I have a conditional formatting rule as =$D1>1 and then I have to create one for each column so =$D2>1 and so on.


It's not ideal to put the dates as rows because the names I have to keep track of is over 500 and growing weekly.


The 2nd condition is the one I need help with.


2nd Condition: I need it to highlight rows if there is a 1 or greater in two of the cells within 4 days or less of each other like in A3. There's a 1 on 3.2.23 and 3.3.23.


If it's possible to combine the 1st condition and the 2nd condition into one rule for each column using the OR function, that's be great.




1 Reply


Yes, it is possible to combine the two conditions into one rule for each column using the OR function. You can use a formula like =OR($D1>1, AND($D1>=1,$D2>=1)) for the first column and then adjust the cell references for each subsequent column.

This formula checks if the value in cell D1 is greater than 1 (first condition) or if both cells D1 and D2 have values greater than or equal to 1 (second condition).

You can then apply this rule to the entire column using the Format Painter or by selecting the entire column and creating a new conditional formatting rule. 


I hope this helps!