Microsoft Office Forums - View Single Post - VBA code to assign color to an active worksheet

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