IF cell Contains "text" then lookup "Colour"

V

vxs

Board Regular
Joined
Dec 10, 2008
Messages
61
Hi all,
I need to format a column/set of cells where each cell has some text in it, and depending on a defined list elsewhere that text does a contains lookup to see whether to colour that cell in Green/Amber or Red. I was going to use the conditional formatting traffic lights but I dont know how to set this rule and do the lookup.

I could use the table below to find out or lookup what colour to use OR the first column with VW in it, in my spreadsheet already has the VW cell conditioned to the correct background colour so could just ask the cell to copy that formatting of cell contains the word VW?

Either-way I am not sure how to go about this, would preferably like to use the traffic light conditional formatting optional but don't know how to match these to the table below. How would I go about this?

Example

[TABLE="width: 500"]
<tbody>[TR]
[TD]VW[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Fiat[/TD]
[TD]Amber[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Land Rover[/TD]
[TD]Amber[/TD]
[/TR]
[TR]
[TD]Kia[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Skoda[/TD]
[TD]Amber[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Jeep[/TD]
[TD]Red[/TD]
[/TR]
</tbody>[/TABLE]


Any help much appreciated.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
so you have a column of words and you want those words to be in cells that have a certain background color based on the example set above?
 
Upvote 0
I see a couple of options. The CF icons are nice, but they don't allow as flexible formulas as you can do when you design your own.

When you said that the column contained some text, I wasn't sure if it contained just the name of the car (Kia) or Kia was in a sentence. I designed a formula that handles both.

GHIJKL
ListCarColor
I own a Nissan.

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B050"]My VW is pretty.[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]VW[/TD]
[TD="bgcolor: #FAFAFA"]Green[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Fiat[/TD]
[TD="bgcolor: #FAFAFA"]Amber[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFD966"]Land Rover[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]BMW[/TD]
[TD="bgcolor: #FAFAFA"]Red[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000"]Jeeps are tough.[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Land Rover[/TD]
[TD="bgcolor: #FAFAFA"]Amber[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFD966"]Fiat[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Kia[/TD]
[TD="bgcolor: #FAFAFA"]Green[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B050"]Kia[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Skoda[/TD]
[TD="bgcolor: #FAFAFA"]Amber[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Honda[/TD]
[TD="bgcolor: #FAFAFA"]Red[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Jeep[/TD]
[TD="bgcolor: #FAFAFA"]Red[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8​



The lookup table I have in K3:L11, and my list is in column H. Select column H, click Conditional Formatting > New Rule > Use a formula > and enter:
=SUMPRODUCT(--ISNUMBER(SEARCH($K$4:$K$11,H1)),--($L$4:$L$11="Green"))*(H1<>"")

Then click on Format... and pick a green fill color.

Repeat the steps twice more, changing Green to Amber and Red, and you'll get the layout as shown above.


Second option is to put this formula in I4:
=MATCH(INDEX($L$4:$L$11,MIN(IF(ISNUMBER(SEARCH($K$4:$K$11,H4)),ROW($K$4:$K$11)-ROW($K$4)+1))),{"Red","Amber","Green"},0)
confirm it with Control+Shift+Enter, and drag down.

Then select column I and pick the traffic lights icon set. You'll get the traffic lights in the next column. (The screen shot doesn't show the icons.)
Row 5, which has no match, gets green because green is the first color in L4:L11.

HIJKL
ListCarColor
I own a Nissan.

<tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #00B050"]My VW is pretty.[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]VW[/TD]
[TD="bgcolor: #FAFAFA"]Green[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Fiat[/TD]
[TD="bgcolor: #FAFAFA"]Amber[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFD966"]Land Rover[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]BMW[/TD]
[TD="bgcolor: #FAFAFA"]Red[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FF0000"]Jeeps are tough.[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Land Rover[/TD]
[TD="bgcolor: #FAFAFA"]Amber[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFD966"]Fiat[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Kia[/TD]
[TD="bgcolor: #FAFAFA"]Green[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #00B050"]Kia[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Skoda[/TD]
[TD="bgcolor: #FAFAFA"]Amber[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Honda[/TD]
[TD="bgcolor: #FAFAFA"]Red[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Jeep[/TD]
[TD="bgcolor: #FAFAFA"]Red[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8​
Let me know if either works for you.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Conditional formatting icon sets for expiry 12months from date in cell
Replies
6
Views
264
MARK858
MARK858
G
  • Solved
COUNTIF a specific coloured text
Replies
6
Views
916
Peter_SSs
Peter_SSs
P
  • Question Question
Applying conditional formatting to merged cells
Replies
1
Views
638
Joe4
Joe4
L
  • Solved
Conditional Formatting Date Ranges
2
Replies
13
Views
2K
Joe4
Joe4
M
  • Question Question
Conditional Format Result - Get Fill Colour
Replies
8
Views
700
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top