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
