VBA code to assign color to an active worksheet

Logit · #4 03-02-2017, 03:07 PM

Paste this macro in the ThisWorkbook module of your project. Save the workbook as Macro Enabled. When the workbook is opened again this macro will be initiated.

You can change the tab color as indicated at the top of the macro by changing the number :

Code:


Private oldindex         As Integer
Const lTAB_COLOUR    As Long = 5   '<--- change color number here
Sub workbook_sheetactivate(ByVal Sh As Object)
   oldindex = Sh.Tab.ColorIndex
   Sh.Tab.ColorIndex = lTAB_COLOUR
End Sub

Sub workbook_sheetdeactivate(ByVal Sh As Object)
   If oldindex <> 0 Then
      If Sh.Tab.ColorIndex = lTAB_COLOUR Then Sh.Tab.ColorIndex = oldindex
   End If
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Inserting Calendar Control Active X Controls on Worksheet	BillCPA	Excel Programming	0	02-18-2016 10:10 AM
Excel - Color Worksheet Tabs	avie layne	Excel	4	11-12-2012 04:45 PM
If color is red then active	ruby_2014	Excel	2	01-09-2012 01:41 AM
Using VBA to change color of a ext box directly on worksheet	Roscoe	Excel Programming	0	11-21-2011 11:42 AM
VB Code in Excel Active worksheet	shakilhyd	Excel	2	05-17-2010 07:50 AM