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.

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.

thanx for your help.

But i've another problem, can i extend the Macro to all the sheets of the
workbook.

Say workbook have 30 sheets. can it be done using the same macro.

Sub test()
Dim sh As Worksheet

For Each sh In Activeworkbook.Worksheets
If sh.Range("A1").Value < "hello" Then
sh.Tab.ColorIndex = 6
Else
sh.Tab.ColorIndex = -4142
End If
Next sh
End Sub


--
HTH

Bob Phillips

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

"Rajat" wrote in message
...
thanx for your help.

But i've another problem, can i extend the Macro to all the sheets of the
workbook.

Say workbook have 30 sheets. can it be done using the same macro.

Thanx a lot for the help Bob