VBA code to assign color to an active worksheet

jane.bugai · #1 03-02-2017, 10:39 AM

Hi All,

I was wondering if there is a way to assign specific color to an active excel worksheet thru Macro/VBA. I have several worksheets in my excel file and as I move thru the sheets, I want the active sheet to turn red. Appreciate everybody's help!

Logit · #2 03-02-2017, 01:39 PM

This is one way to do it. I'm certain there are others.

Paste this into the sheet level module of the sheet you want to 'color'.
You can change the vbColor should you want one sheet colored different from the others.

Code:

Option Explicit

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
With Application.ActiveSheet
    Cells.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .Color = vbYellow    '<-- change color here
        .PatternTintAndShade = 0
    End With
End With
Application.ScreenUpdating = True
End Sub

jane.bugai · #3 03-02-2017, 02:36 PM

Thank you so much for your reply! It worked but I think I didn't explain my question correctly.

I would like the active tab itself to change the color, not the entire worksheet. If that makes sense. In excel, all tabs are white by default and I want the tab I am currently in to change color from white to red. And if I move to another tab, then new tab becomes red and the old one is white again.

Thank you in advance!

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

jane.bugai · #5 03-03-2017, 08:07 AM

This is great! Thank you

Works really well. Appreciate your help!

Logit · #6 03-03-2017, 08:39 AM

You are welcome. Glad to help.

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