#Excel Impossibly Easy #2: Change Sheet Tab Color with a User-Defined Worksheet Formula

What if I told you that I wanted to change tab colors on sheets in a workbook by entering a formula (UDF) on a worksheet. Impossible, right? No, it turns out that it is “easy”.

This simple UDF (code shown below) can be entered on a worksheet and the desired worksheet tab will change to any color you want.

Function ChangeTabColor(sht As String, RED_Color As Integer, GREEN_Color As Integer, BLUE_Color As Integer)

With ActiveWorkbook.Sheets(sht).Tab

.Color = RGB(RED_Color, GREEN_Color, BLUE_Color)

End With

End Function

For example, entering this formula in a cell will turn the tab on Sheet1 red.

=ChangeTabColor(“Sheet1”,255,0,0)

This figure shows the result in the example workbook of entering two cells. Note that the UDF does not have to be entered on the worksheet whose tab color is changed.

ExcelImpossiblyEasy#2_1

I have added a worksheet that has a list of colors along with their respective RGB codes for your convenience. I am sure that you will come up with many novel ways to use this technique.

The example file can be downloaded here.

TabColor

Advertisements

4 thoughts on “#Excel Impossibly Easy #2: Change Sheet Tab Color with a User-Defined Worksheet Formula

  1. Pingback: #Excel Super Links #144 – shared by David Hager | Excel For You

  2. Pingback: A formula to change the colour of a worksheet tab | Anthony Yager Management Consultant

  3. Pingback: Christmas Lights Using Excel Sheets | My Spreadsheet Lab

  4. Anklebuster

    Hi David,
    I came here by way of Excel Off the Grid. Mark had some nice things to say about you and your site. First, hats off for sharing this tab-coloring technique.

    Second, believe it or not, the UDF excites me because it opens up visual possibilities for some game ideas. So, of course, I’m thrilled to see that you have at least one game on the site.

    Finally, I look forward to learning and discovering cool things here!

    Cheers,

    Mitch

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s