Can I add More than three condition in conditional formating

[Guest]

Thanks Bob for devoting your most valuable time in solving my problem, but I
am not very familiar with these codes. I tried by putting these codes into
sheet but I am not able to get desired result. Can you suggest me how can I
make these codes helpful for me or can you change these codes in the
appropriate form so that by just putting them into sheet I would be able to
get desired result. I shall be very greatful to you.

Thanks & regards

Jai

Using code


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

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 "SL": .Interior.ColorIndex = 3 'red
Case "CL" .Interior.ColorIndex = 6 'yellow
Case "PL" .Interior.ColorIndex = 5 'blue
Case "ML": .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

I am making an attendence sheet, in which I have to put SL(sick leave),
CL(casual leave), PL(marriage leave), ML(Maternity Leave). I wanted to
highlight automatically these leave with different colour by the help of
conditional formating. Is it possible to add condition more than three in
conditional formating.
Jai

 

[Don Guillett]

The basic problem is that there were a couple of : missing. I also made
allowance for lower case by user and other text.
I would have done it more like this.
Very important that you place in a sheet module instead of a regular module.
right click sheet tab>view code>insert this
Also change "H1:H10" to suit your need


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

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = xlNone
Select Case UCase(.Value)
Case "SL": x = 3 'red
Case "CL": x = 6 'yellow
Case "PL": x = 5 'blue
Case "ML": x = 10 'green
Case Else
End Select
.Interior.ColorIndex = x
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub