MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If then statement


Posted by Steve Bomhoff on February 12, 2002 8:33 AM

Does anyone know how to have a cell automatically highlight to a color if there is a specific character in that cell? For example, a cell will have the letter "X" in it and I would like for it to be filled with red color or if it has a + in it it will be filled with yellow color. Any help is appreciated.


Posted by Skrappy on February 12, 2002 8:43 AM

Use conditional formatting under the format menu. You would use if "cell value is equal to X" then select red under options.

Posted by Aladin Akyurek on February 12, 2002 8:47 AM

Select the range of interest.
Activate Format|Conditional Formatting.
Choose "Formula Is" for Condition 1.
Enter as formula:

=AND(ISNUMBER(SEARCH("X",A1)),NOT(ISNUMBER(SEARCH("+",A1))))

Activate Format.
Choose red on the Patterns tab.
Click OK.
Activate Add.
Choose "Formula Is" for Condition 2.
Enter as formula:

=AND(ISNUMBER(SEARCH("+",A1)),NOT(ISNUMBER(SEARCH("X",A1))))

Activate Format.
Choose yellow on the Patterns tab.
Click OK, OK.

If you rather have an upper case X as condition, replace SEARCH by FIND in the above formulas.

=========

Posted by Tom Urtis on February 12, 2002 8:48 AM

In addition to Skrappy's idea, you can try this approach. One advantage is that you are not limited to 3 conditions, in case you want to have more than 3 characters determine the cell color.

Right click on your sheet tab, left click on View Code, and paste this in.

This code is in a Case structure, so you can easily modify it or add to it if you need to in the future.

'''''''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Select Case Target

Case "X"
Target.Interior.ColorIndex = 3

Case "+"
Target.Interior.ColorIndex = 6

Case Else
Target.Interior.ColorIndex = 0

End Select

End Sub

'''''''''''''''''''''''''''''''''''''

HTH

Tom Urtis