Re: Macro Excell 03/07

[Gord Dibben]

Works fine for me in Excel 2003 with 58 sheets in a workbook.

But I would not color sheet tabs to white.

That is the default color for grouped sheets and may cause some confusion.

Gord Dibben MS Excel MVP

On Mon, 4 Jan 2010 13:57:01 -0800, Mike <Mi...@discussions.microsoft.com>
wrote:

>This is the macro im using for Excell 07 to change Tab colours on each sheet
>(i have 52 sheets & works fine & when i use it for Excell 03 i get a run time
>error to debug Why & is there any other solutions or macro to use on both
>
>Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> ByVal Target As Range)
>
> Dim myRngToInspect As Range
>
> Set myRngToInspect = Sh.Range("C300")
>
> If Target.Cells.Count > 1 Then
> Exit Sub
> End If
>
> If Intersect(Target, myRngToInspect) Is Nothing Then
> Exit Sub
> End If
>
> If UCase(Target.Value) = UCase("QC") Then
> Sh.Tab.ColorIndex = 3 'red for me
> Else
> Sh.Tab.ColorIndex = 2 'White for me
> End If
>
>End Sub
>
>
>

[Dave Peterson]

It worked fine for me in xl2003, too.

What error message did you see?

Which line caused the error?

Mike wrote:
>
> This is the macro im using for Excell 07 to change Tab colours on each sheet
> (i have 52 sheets & works fine & when i use it for Excell 03 i get a run time
> error to debug Why & is there any other solutions or macro to use on both
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> ByVal Target As Range)
>
> Dim myRngToInspect As Range
>
> Set myRngToInspect = Sh.Range("C300")
>
> If Target.Cells.Count > 1 Then
> Exit Sub
> End If
>
> If Intersect(Target, myRngToInspect) Is Nothing Then
> Exit Sub
> End If
>
> If UCase(Target.Value) = UCase("QC") Then
> Sh.Tab.ColorIndex = 3 'red for me
> Else
> Sh.Tab.ColorIndex = 2 'White for me
> End If
>
> End Sub

--

Dave Peterson