Conditional Formatting

[jhelliar]

I Would like to use more than 3 conditional formats but the visual basic code I found on the net only worked if I typed the number in and not if it was the result of a formula. Is there any way of getting it to work with formulae? I Have included the code below:

'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:A10")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0 To 10
Target.Interior.ColorIndex = 5
Case 10 To 20
Target.Interior.ColorIndex = 10
Case 21 To 30
Target.Interior.ColorIndex = 6
Case 31 To 40
Target.Interior.ColorIndex = 46
Case 41 To 50
Target.Interior.ColorIndex = 45
End Select
End If
End Sub
'END OF CODE

[VBA Noob]

Closed.

Duplicate post. Please read forum rules below

VBA Noob