Coloring cells based on text in another cell.

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
So I have cell 'A3' that may have a variety of words or phrases typed into it. if the term 'unused' appears I want cells B3:D3 to have a background color filled in. I think this is probably really simple and I am just missing it.
Thanks,
Andrew
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
1) Highlight columns B:D
2) Select Format > Conditional Formatting
3) Use these settings:

Condition1:

Formula Is:
=ISNUMBER(SEARCH("unused", $A1))

Format...Pattern:
Select a color
 

Eric Livesay

Board Regular
Joined
Feb 13, 2008
Messages
126
to piggy back on this subject how could i change the formula to format A1 to change colors based on if B1 is Blank?
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
1) Highlight column A
2) Select Format > Conditional Formatting
3) Use these settings:

Condition1:

Formula Is:
=ISBLANK($B1)

Format...Pattern:
Select a color
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,562
Office Version
365
Platform
Windows
Andrew

A couple of points about your first question.

1. You don't need the ISNUMBER when used in Conditional Formatting. This should do the same thing
=SEARCH("unused",$A3)

2. It may not occur with "unused" but suppose you are interested in the word "his" and A3 contained "This is clever". The suggested CF formula structure would highlight the cells. If you only want to highlight when the word itself is found, try this sort of formula in your CF

=SEARCH(" his "," "&$A3&" ")
 

Forum statistics

Threads
1,084,884
Messages
5,380,436
Members
401,678
Latest member
saffar

Some videos you may like

This Week's Hot Topics

Top