Worksheets also have a codename that you can use. It starts the same as the
sheet name, but if the sheet name is changed. the codename isn't

Sub test()
If Sheet1.Range("A1").Value < "hello" Then
Sheet1.Tab.ColorIndex = 6
Else
Sheet1.Tab.ColorIndex = -4142
End If
End Sub


--
HTH

Bob Phillips

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

"Rajat" wrote in message
...
i used the following VBA code to chage the colour of using a condition -
Sub test()
If Sheets("Sheet1").Range("A1").Value < "hello" Then
Sheets("Sheet1").Tab.ColorIndex = 6
Else
Sheets("Sheet1").Tab.ColorIndex = -4142
End If
End Sub
----------------------------------
my problem is that if rename the worksheet from "Sheet1" to say "1" the
macro has an Run Time Error 9.
I changed the VBA code to "Sheet1" to "1" but nothing happened, help
requested.