#1
|
|||
|
|||
VBA code to assign color to an active worksheet
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! |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
VBA code to assign color to an active worksheet
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! |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
This is great! Thank you
Works really well. Appreciate your help! |
#6
|
|||
|
|||
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 |