Conditional formatting multiple cells

New Contributor



I'm currently working on a spreadsheet where you can fill in which jobs are done and on what date they were completed. At the end their are 2 columns labelled Query Issued and Query answered in case their is an issue someone noticed with a piece of work and we can note when the query was answered/if it is still yet to be answered.

I have used conditional formatting to colour the cells when certain cells are filled. For example cell K4 and L4 are blank, when a date is entered into K4 (Query raised), L4 will turn red until a date is entered into its cell showing the query has been answered which will then turn L4 green. I did this by using the formula ( =IF($K$4="",FALSE,TRUE) ) and ( =IF($L$4="",FALSE,TRUE) ).

They both work however my issue is that their are 500 pieces of work and to input that into each cell would be very time consuming.

Does anyone know how to input that formula into every cell within the column to where it would automatically change the respective cells (K45 & L45, K201 & L201 etc.)

If it is not possible then no worries but if anyone does know a way it would be a huge help




3 Replies
best response confirmed by ExyteNLowe (New Contributor)

Hello @ExyteNLowe,


You will need to remove the absolute references from your formula.

Change your conditional format formulas to the following:


and in the Applies to section of the Conditional Formatting Rules Manager, apply the rule to the necessary range.


That's worked perfectly, Thanks



Happy to help!