conditional formatting more than 3 conditions

[Guest]

I have column A conditional formatted as =D1=1, but I need to be able to compare it to 10 different number in D1, how can this be done, I am using excel '97. thanks

 

[Tommy Bak]

if each cell in column A relates to a cell in the same row,but in column B you could use this macro. (to insert rightckick on the sheettab, choose show code and paste the code)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Select Case Target.Value
Case 1: ci = 1
Case 2: ci = 3
Case 3: ci = 5
Case 4: ci = 7
Case 5: ci = 9
Case 6: ci = 11
Case 7: ci = 13
Case 8: ci = 15
Case 9: ci = 17
Case 10: ci = 18
Case Else
End Select
Target.Offset(0, -3).Interior.ColorIndex = ci
End If
End Sub

 

[Guest]

Tommy that works! but if I put a letter in column D I get type mismatch, can the be fixed? Thanks

 

[Tommy Bak]

Sorry. I tested it in xl2000 with no problems.
In xl97 this macro will do the job.
Just added a check to see if target.value is numeric

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
If IsNumeric(Target.Value) Then
Select Case Target.Value
Case 1: ci = 1
Case 2: ci = 3
Case 3: ci = 5
Case 4: ci = 7
Case 5: ci = 9
Case 6: ci = 11
Case 7: ci = 13
Case 8: ci = 15
Case 9: ci = 17
Case 10: ci = 18
Case Else
End Select
Target.Offset(0, -3).Interior.ColorIndex = ci
End If
End If
End Sub

regards Tommy

 

[Guest]

Thanks tommy that did it!