Conditional formatting based on multiple cell formulas

Copper Contributor

Hello, when a user enter a value in the "Applicable" column in the table below, the cell "Confirmed on" changed color. This is meant to remind them they need to enter a date. I use Conditional Formatting / Use formula.../=$A$9<>"" and it works.


However, I am not able to make the cell resume to initial color once the date has been entered. I tried using Conditional formatting still, with formula  =AND($A$9<>"",$B$9<>""). 


Also, once this is done, how do you populate all columns with the formatting? Dragging down A1 and B1 does not work. Thank you in advance!


PS I do not know VBA. 


Applicable Y|NConfirmed on
2 Replies


First priority rule for conditional formatting:


Second priority rule for conditional formatting:



These are the rules for conditional formatting in my example. The rules are in order of their priority in the manager for conditional formatting.



The format is applied to this range in my example.

conditional formatting.png



Select the range where you want to apply conditional formatting:



Select conditional formatting--> new rule



choose :


Enter the formula: =AND(A2<>"",B2="")



choose your format and click OK.





this will apply conditional formatting to complete selected range, formula that you write should be valid for first cell of the selection, it will automatically dragged down for rest of the selection.