Conditionally Format one cell based on partial text of a different cell

[Rosstamon]

Hello all and happy Sunday.

I'm trying to use conditional formatting to do the following.

Cell F6 is a drop down box (Data Validation)
2 of the 4 selections contain the word "Consultant".

The target cells are B29 thru F33.

I'm trying to figure out how to create a conditional format for cells B29 thru F33 that says:
--If F6 contains the word "Consultant" then do not format these cells
--If F6 does not contain the word "Consultant" then FILL these cells RED

Any help would be most apprecaited.
I'm not well versed with VBA, so hoping to avoid it.

 

[MARZIOTULLIO]

Hi

Try

First, select all the cells in range(B29:F33) you want formatted


Conditional formatting




New Rule


Use a formula to determine which cells to format


Format values where this formula is true copied :


=$F$6<>"Consultant"




set the parameter to formatting to coloring the cells ok


ok


Applies to =$B$29:$F$33 ok

 

[JoeMo]

Hello all and happy Sunday.

I'm trying to use conditional formatting to do the following.

Cell F6 is a drop down box (Data Validation)
2 of the 4 selections contain the word "Consultant".

The target cells are B29 thru F33.

I'm trying to figure out how to create a conditional format for cells B29 thru F33 that says:
--If F6 contains the word "Consultant" then do not format these cells
--If F6 does not contain the word "Consultant" then FILL these cells RED

Any help would be most apprecaited.
I'm not well versed with VBA, so hoping to avoid it.

Select cells B29:F33 > Home>Conditional Formatting>New Rule>Use a formula

Formula is:

=NOT(ISNUMBER(SEARCH("Consultant",$F$6)))

Apply the format you want and click OK.

 

[Rosstamon]

JoeMo,
Many many thanks to you. This works exactly as I needed. I very much appreciate your help and wisdom.

 

[Rosstamon]

Hello,
Thank you for replying so quickly. This didn't work for me. It formatted the cells no matter what F6 said. I'm sure if I fiddled around with this one I could get it to work, but JoeMo's reply did work so I'll go with that. Thanks again for working on this.