Conditional Formatting
Posted by Mike on January 30, 2002 4:44 AM
Conditional formatting only allows up to three alternative formats to be applied to a cell or a range of cells. I need several formats depending upon a letter entered into cells. Is there a macro solution that would help?
Posted by Tom Urtis on January 30, 2002 5:22 AM
Here's a suggestion that you might try for more than 3 conditional formats.
It does not trigger with formulas, or if the code is changed after values are entered, just as you would expect at the worksheet change event level.
Notice the text is in quotes, with examples here for single letters, or nice words such as "Tom". These text cases are case sensitive. Also, I threw in an example for numbers, for instance if you enter a number between 16 and 20.
Modify the code to suit the conditional formatting you want to achieve.
Right click on your sheet tab, left click on View Code, and paste this in:
-------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myRange As Range
Set myRange = Range("A1:C20")
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Intersect(Target, myRange) Is Nothing Then
Set myRange = Nothing
Exit Sub
End If
Select Case Target
Case "a"
Target.Interior.ColorIndex = 11
Case "B"
Target.Interior.ColorIndex = 21
Case "Cat"
Target.Interior.ColorIndex = 5
Case 16 To 20
Target.Interior.ColorIndex = 35
Case Is = "Tom"
Target.Interior.ColorIndex = 3
'Otherwise leave cell interiors alone
Case Else
Target.Interior.ColorIndex = 0
End Select
End Sub
-------------------------------------
Any help?
Tom Urtis
-------------------------------------
