Conditional formatting

[lauren18]

Hi all,

I'm looking to create a formula for conditional formatting that works as thus;

- If D4 to D401 contain the specific text "Bronze" then the row (A through to Z) it is in will be colour1
- If D4 to D401 contain the specific text "Silver" then the row (A through to Z) it is in will be colour2
- If D4 to D401 contain the specific text "Gold" then the row (A through to Z) it is in will be colour3
- If D4 to D401 contain the specific text "Platinum" then the row (A through to Z) it is in will be colour4

These words appear in other columns however I only want to look for the word to appear in column D and then the colour will be applied to the whole row.

i.e. if D4 has the word gold then cells A4 to Z4 will be colour3
if D5 has the word silver then cells A5 to Z5 will be colour2

 

[ping_pong]

No need for VBA, if you are familiar with conditional formatting already this should be easy. You will make a conditional format based on a formula. In your case, =ISNUMBER(SEARCH("Bronze",$D4)). Apply this to the entire range, A4:Z401. You may need to apply it then reopen the formula and correct the reference.

 

[jtakw]

Hi,

If I remember correctly, if you have Excel NEWER than 2007, you can use the following FOUR (4) conditional formatting rules, highlight A4:Z401, format using formulas:

=$D4="Bronze"

format color1

=$D4="Silver"

format color2

=$D4="Gold"

format color3

=$D4="Platinum"

format color4

If you have Excel 2007 or older, you'll need VBA solution.

 

[ping_pong]

Note that the difference between my method and jtakw's is that if your cell must equal, not contain, "Bronze", "Silver", etc for jtakw's to work.

 

[jtakw]

Hi,

If D4 has other text or values in addition to "Bronze", "Silver", etc...., select A4:Z401, use:

=SEARCH("Bronze",$D4)
=SEARCH("Silver",$D4)
=SEARCH("Gold",$D4)
=SEARCH("Platinum",$D4)

Format colors needed.