Coloring an Excel tab through VB Code

Recently, I had a project where I was populating more than 1 worksheet in an auto generated spreadsheet.  Setting the tab name is helpful but adding an alternate color really sets the tabs apart.

This post will show you how to do that.

To test this code, open a new Excel spreadsheet.

Press Alt-F11 to open the Visual Basic editor.

Click the menu Insert | Module

Paste the following code block:

[code]

Sub ColorTab()
   worksheets(1).Name = "New Tab Name"
   worksheets("New Tab Name").tab.color = RGB(255,0,0)
End Sub

[/code]

Switch back to the Excel Application and press Alt-F8 to bring up the Macro dialog box.  You should see the title of our new subroutine in the box.  Highlight ColorTab and press Run.

Your result should be that your first worksheet now is renamed and colored red.

 

EXPLANATION:

The renaming of the tab is fairly straight forward.  You can access the first worksheet by passing in the index of 1.  Rename the worksheet by passing the new name to the .Name property.

You can also access a particular worksheet by passing the tab name instead of the index.  To set the color of the tab use the .tab.color of the worksheet.  You must pass in a color value by using the RGB function.

The RGB function takes three integers between 0 and 255 representing a Red, Green, and Blue component.  The function results in a color value.

To help in creating the desired color combination, you can use varous tools to find the RGB values.  A tool I found online can be found here:  http://www.psyclops.com/tools/rgb/