Change Tab colour using Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rajat - 24 Oct 2006 08:10 GMT

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.

Reply to this Message

Bob Phillips - 24 Oct 2006 09:23 GMT

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

Signature

HTH

Bob Phillips

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

> i used the following VBA code to chage the colour of using a condition -
> Sub test()
[quoted text clipped - 9 lines]
> I changed the VBA code to "Sheet1" to "1" but nothing happened, help
> requested.

Reply to this Message

Rajat - 24 Oct 2006 09:49 GMT

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.

Reply to this Message

Bob Phillips - 24 Oct 2006 11:43 GMT

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

Signature

HTH

Bob Phillips

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

> 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.

Reply to this Message

Rajat - 24 Oct 2006 14:31 GMT

Thanx a lot for the help Bob

Reply to this Message