Conditional Formating - More then 3 conditioned with formula

[Guest]

Hi,
I viewed the VB code that was suggested to overcome many cond. formating.
BUT
It does not work properally when the conditioned range is based on formula.

I dont mind activating mannually a routine instead.
TIA

 

[Bob Phillips]

What code are you referring to?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob,
the one you supplied to sara on 10/21

What code are you referring to?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Can you post it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "Open":
..Interior.ColorIndex = 3 'red
Case "For Review / Approval":
..Interior.ColorIndex = 6 'yellow
Case "Verified":
..Interior.ColorIndex = 5 'blue
Case "Closed":
..Interior.ColorIndex = 10 'green
Case "Pending":
..Interior.ColorIndex = 38 'rose
Case "Rejected":
..Interior.ColorIndex = 37 'pale blue
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub