Excel 2007 : conditioning formatting a cell if another cell contain...

[alex_shin]

Hi All,

If I have two columns A and B; column A contain text with "Bottle 123" and column B contain "B123". How can I use conditioning formatting on Column B, if column A contain the text "Bottle", highlight cell in column B green.


Kind regards,
Alex

[SCLai]

Welcome to the forums

Please try this:
Given "Bottle 123" is in A1
1.) Select Cell B1
2.) Under conditional formatting, select "Formula is"
3.) =SEARCH("*Bottle*",$A$1,1)
4.) Select desired formatting

If you wish to apply this to the whole of column B:
5a.) Select the whole of column B instead of only B1, then carry out step 2 ~ 4
OR
5b.) After carrying out step 1 ~ 4, copy Cell B1, then select Column B > Right Click > Paste Special > Formats

Hope this is what you are looking for!

SC

[alex_shin]

Thanks SCLai. You are getting to nearly what I want :-)

A B
Bottle 123 B123
Bottle 345 B345
Bottle 123 C123
Bottle 123 KA22

In the above table, I want conditioning formatting in column B:-

a.) Cell 2 in Column B turn green if
i.) Cell 2 in Column A contain the word "Bottle"
ii.) Cell 2 in Column B start with letter "B"

b.) Cell 2 in Column B turn red if
i.) Cell 2 in Column A contain the word "Bottle"
ii.) Cell 2 in Column B does not start with letter "B"

[Fotis1991]

Hi

..Then, highlight your range and try this.

1) =AND(LEFT($B1)="B",SEARCH("*Bottle*",$A1,1) >>Green

2) =AND(LEFT($B1)<>"B",SEARCH("*Bottle*",$A1,1)>>Red.

Is this, works for you?

[alex_shin]

Thanks Fotis!

Brilliant!

[Fotis1991]

You are welcome!

Pls, mark your Thread, as Solved(See at the top of the page to see how to do this).