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

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 tabview codeinsert 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


--
Don Guillett
SalesAid Software

"jai" wrote in message
...
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