#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Macro Excell 03/07

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




  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro Excell 03/07

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




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Macro Excell 03/07

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro options in Excell 2003 Joshua Excel Worksheet Functions 1 April 30th 08 07:16 PM
Excell Macro Help Please Bill Kirk New Users to Excel 2 January 25th 06 10:22 PM
Excell freezez after many macro cycles. Vector Excel Discussion (Misc queries) 0 January 11th 06 06:39 AM
converting Lotus 123 macro to Excell macro mark h Excel Discussion (Misc queries) 6 July 11th 05 01:28 PM
Macro Text into Excell Krefty Excel Discussion (Misc queries) 2 December 20th 04 10:14 PM


All times are GMT +1. The time now is 01:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"