SOLVED

Conditional formatting not working

%3CLINGO-SUB%20id%3D%22lingo-sub-1372191%22%20slang%3D%22en-US%22%3EConditional%20formatting%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372191%22%20slang%3D%22en-US%22%3EHi%20there.%3CBR%20%2F%3EIm%20looking%20for%20help%20with%20my%20conditional%20formatting.%20Im%20creating%20a%20leave%20planner%20and%20i%20want%20all%20of%20the%20same%20type%20of%20leave%20to%20be%20the%20same%20colour.%20So%3CBR%20%2F%3E%3CBR%20%2F%3EAL%20in%20pink%3CBR%20%2F%3EAL%20AM%20in%20pink%3CBR%20%2F%3EAL%20PM%20im%20pink%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20so%20on%20for%20lots%20of%20leave%20types.%20I%20had%20managed%20this%20and%20it%20works%20fine%20on%201%20spreadsheet%20but%20when%20using%20it%20on%20another%20the%20AL%20PM%20stays%20white.%3CBR%20%2F%3E%3CBR%20%2F%3EIm%20am%20selecting%20cell%20%3D%20to%20and%20then%20the%20above%20in%203%20seperate%20rules.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20found%20it%20worked%20on%20a%20small%20range%20but%20not%20when%20i%20extended%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance.%3CBR%20%2F%3EDiane%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1372191%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372194%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372194%22%20slang%3D%22en-US%22%3Esometimes%20the%20ranges%20and%20cell%20references%20can%20get%20messed%20up.%20double%20check%20that%20the%20'applied%20to'%20range%20in%20the%20conditional%20formatting%20is%20still%20correct.%20otherwise%2C%20maybe%20send%20the%20file%20or%20screenshots%20or%20something%20for%20us%20to%20go%20by%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1378499%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378499%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3EThanks%20for%20your%20reply%2C%20sorry%20mine%20is%20so%20late%2C%20I%20have%20a%20very%20small%20baby.%20I%20have%20double%20checked%20it%20against%20the%20spreadsheet%20that%20actually%20works%20and%20still%20cant%20see%20where%20I%20have%20gone%20wrong.%20The%20only%20change%20I%20have%20made%20since%20it%20worked%20is%20add%20a%20column%20after%20every%20column%20and%20an%20overview%20page%20to%20calculate%20totals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20could%20have%20a%20look%20at%20the%20attached%20that%20would%20be%20a%20great%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EDiane%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1378644%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660172%22%20target%3D%22_blank%22%3E%40diseydaly1983%3C%2FA%3E%26nbsp%3B%20Your%20problem%20is%20that%20the%20data%20validation%20is%20pointing%20to%20the%20Overview%20sheet%20where%20the%20value%20for%20%22FL%22%20is%20actually%20%22FL%20%22%20(notice%20the%20space%20at%20the%20end).%26nbsp%3B%20Then%20the%20conditional%20formatting%20is%20set%20as%20Value%20%3D%20%22FL%22%20and%20%22FL%22%20does%20not%20exactly%20%3D%20%22FL%20%22.%3C%2FP%3E%3CP%3EI%20recommend%20using%20the%20%22Specific%20Text%22%20%22Starts%20With%22%20option%20or%20%22Contains%22%20option%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor
Hi there.
Im looking for help with my conditional formatting. Im creating a leave planner and i want all of the same type of leave to be the same colour. So

AL in pink
AL AM in pink
AL PM im pink

And so on for lots of leave types. I had managed this and it works fine on 1 spreadsheet but when using it on another the AL PM stays white.

Im am selecting cell = to and then the above in 3 seperate rules.

I found it worked on a small range but not when i extended it.

Thanks in advance.
Diane
5 Replies
Highlighted
sometimes the ranges and cell references can get messed up. double check that the 'applied to' range in the conditional formatting is still correct. otherwise, maybe send the file or screenshots or something for us to go by
Highlighted

@mtarlerThanks for your reply, sorry mine is so late, I have a very small baby. I have double checked it against the spreadsheet that actually works and still cant see where I have gone wrong. The only change I have made since it worked is add a column after every column and an overview page to calculate totals.

 

If you could have a look at the attached that would be a great help.

 

Thanks

Diane

Highlighted
Best Response confirmed by diseydaly1983 (Occasional Contributor)
Solution

@diseydaly1983  Your problem is that the data validation is pointing to the Overview sheet where the value for "FL" is actually "FL " (notice the space at the end).  Then the conditional formatting is set as Value = "FL" and "FL" does not exactly = "FL ".

I recommend using the "Specific Text" "Starts With" option or "Contains" option

 

Highlighted
Thanks very much. Im not great on excel but when i know something can be done i like to try.
Highlighted
glad I could help and it always good to try, because that is how we learn.