Excel - Conditional Format Cell based on previous cell 2

[JohnOB]

I am trying to work out how to use conditional formatting to shade a cell if the cell to the left of it contains the word "full". It will contain other text so for example may say "annual leave full day" or "full day training course" etc.

I have tried various combinations but no luck so far.

Thanks

"Stupid isn't not knowing the answer, it's not asking the question

 

[Loomah]

from the conditional formatting dialog choose formula is and enter
=search("full",a1)

set formatting as required

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

sorry, forgot to say the formula above is for formatting cell b1!

use find instead of search if case matters, ie search will find "full" anf "FULL". find would only find "full"

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

sorry again - not thinking here. formula should be
=search("full",a1)>0

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[JohnOB]

Thanks Loomah, the first formula seems to work perfectly this was not a function that I had heard of before.

I control the contents of the other cell with data validation so I know the same case will always be used

Thanks again

Regards,

John

"Stupid isn't not knowing the answer, it's not asking the question

 

[MJPPaba]

if the conditional formatting needs to be copied down to a defined range you might think of using the R1C1 format of cell referencing rather than using A1, to chenge this go to Tools options and change the setting in there. For the cell to the left of the current cell this would be =search("full",RC[-1])>0


Cheers

Pabs