SOLVED

Conditional formatting multiple cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1749162%22%20slang%3D%22en-US%22%3EConditional%20formatting%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749162%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20working%20on%20a%20spreadsheet%20where%20you%20can%20fill%20in%20which%20jobs%20are%20done%20and%20on%20what%20date%20they%20were%20completed.%20At%20the%20end%20their%20are%202%20columns%20labelled%20Query%20Issued%20and%20Query%20answered%20in%20case%20their%20is%20an%20issue%20someone%20noticed%20with%20a%20piece%20of%20work%20and%20we%20can%20note%20when%20the%20query%20was%20answered%2Fif%20it%20is%20still%20yet%20to%20be%20answered.%3C%2FP%3E%3CP%3EI%20have%20used%20conditional%20formatting%20to%20colour%20the%20cells%20when%20certain%20cells%20are%20filled.%20For%20example%20cell%20K4%20and%20L4%20are%20blank%2C%20when%20a%20date%20is%20entered%20into%20K4%20(Query%20raised)%2C%20L4%20will%20turn%20red%20until%20a%20date%20is%20entered%20into%20its%20cell%20showing%20the%20query%20has%20been%20answered%20which%20will%20then%20turn%20L4%20green.%20I%20did%20this%20by%20using%20the%20formula%20(%20%3DIF(%24K%244%3D%22%22%2CFALSE%2CTRUE)%20)%20and%20(%26nbsp%3B%3DIF(%24L%244%3D%22%22%2CFALSE%2CTRUE)%20).%3C%2FP%3E%3CP%3EThey%20both%20work%20however%20my%20issue%20is%20that%20their%20are%20500%20pieces%20of%20work%20and%20to%20input%20that%20into%20each%20cell%20would%20be%20very%20time%20consuming.%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20to%20input%20that%20formula%20into%20every%20cell%20within%20the%20column%20to%20where%20it%20would%20automatically%20change%20the%20respective%20cells%20(K45%20%26amp%3B%20L45%2C%20K201%20%26amp%3B%20L201%20etc.)%3C%2FP%3E%3CP%3EIf%20it%20is%20not%20possible%20then%20no%20worries%20but%20if%20anyone%20does%20know%20a%20way%20it%20would%20be%20a%20huge%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENathaniel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1749162%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749229%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749229%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F821911%22%20target%3D%22_blank%22%3E%40ExyteNLowe%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20need%20to%20remove%20the%20absolute%20references%20from%20your%20formula.%3C%2FP%3E%3CP%3EChange%20your%20conditional%20format%20formulas%20to%20the%20following%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DIF(K4%3D%22%22%2CFALSE%2CTRUE)%20and%20%3DIF(L4%3D%22%22%2CFALSE%2CTRUE)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3Eand%20in%20the%20Applies%20to%20section%20of%20the%20Conditional%20Formatting%20Rules%20Manager%2C%20apply%20the%20rule%20to%20the%20necessary%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749251%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20multiple%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20worked%20perfectly%2C%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

 

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

 

Thanks

Nathaniel

3 Replies
Highlighted
Best Response confirmed by ExyteNLowe (New Contributor)
Solution

Hello @ExyteNLowe,

 

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

Change your conditional format formulas to the following:

=IF(K4="",FALSE,TRUE) and =IF(L4="",FALSE,TRUE)

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

Highlighted

@PReagan 

That's worked perfectly, Thanks

Highlighted

@ExyteNLowe 

 

Happy to help!