Conditional formating
 

Hi i'm using the code below for conditional formating, this work fine but is
there a code where if I chose a criteria which is not in the code to change
the colour to default (white). Currently I am using validation box and has
upto 16 different criteria, if I type in LEX, this changes the cell colour to
Red, but if i change the same cell value to LL, the cell colour is still Red
and not white.

Can you anyone help me please.

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "G7:HC600"

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 "LEX": .Interior.ColorIndex = 3 'red
Case "LIN": .Interior.ColorIndex = 6 'yellow

End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Thanks

Yogin

Conditional formating
 

Just add a line:

Select Case .Value
Case "LEX": .Interior.ColorIndex = 3 'red
Case "LIN": .Interior.ColorIndex = 6 'yellow
Case Else: .Interior.ColorIndex = xlNone
End Select



Yogin wrote:

Hi i'm using the code below for conditional formating, this work fine but is
there a code where if I chose a criteria which is not in the code to change
the colour to default (white). Currently I am using validation box and has
upto 16 different criteria, if I type in LEX, this changes the cell colour to
Red, but if i change the same cell value to LL, the cell colour is still Red
and not white.

Can you anyone help me please.

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "G7:HC600"

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 "LEX": .Interior.ColorIndex = 3 'red
Case "LIN": .Interior.ColorIndex = 6 'yellow

End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Thanks

Yogin

--

Dave Peterson

Conditional formating
 

Thanks Dave,
This worked perfectly.

Yogin

"Dave Peterson" wrote:

Just add a line:

Select Case .Value
Case "LEX": .Interior.ColorIndex = 3 'red
Case "LIN": .Interior.ColorIndex = 6 'yellow
Case Else: .Interior.ColorIndex = xlNone
End Select



Yogin wrote:

Hi i'm using the code below for conditional formating, this work fine but is
there a code where if I chose a criteria which is not in the code to change
the colour to default (white). Currently I am using validation box and has
upto 16 different criteria, if I type in LEX, this changes the cell colour to
Red, but if i change the same cell value to LL, the cell colour is still Red
and not white.

Can you anyone help me please.

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "G7:HC600"

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 "LEX": .Interior.ColorIndex = 3 'red
Case "LIN": .Interior.ColorIndex = 6 'yellow

End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Thanks

Yogin

--

Dave Peterson