Did you know that an Excel UDF can change the color of a sheet tab? David Hager’s post shows us how to do this! Somehow I came up with the idea to create Christmas Lights using Excel sheet tabs!

Too Late for Christmas & New Years?

But isn’t it too late for this post? It’s Jan 4th! According to Seinfeld, I might be ok. What do you think?

Original Idea from David Hager

David Hager’s excel blog is great! He shares so much knowledge. Thanks David for the UDF!

David’s UDF

What is a UDF? Read this from Vertex42. Here is David’s UDF

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

Enter the above UDF code into a module. Now you can use the UDF like a normal function.

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

The formula above should make Sheet1 green.

Make UDF Inputs Dynamic!

David’s UDF has 4 inputs: sheetname and three numbers for Red, Green and Blue. My idea was to change these hard coded inputs into variables and have some fun! What could I make?

Christmas Lights?!?!

As my wife has pointed out, our house is one of the few houses in the area that doesn’t have lights. How could I remedy this? It’s much too cold to string up lights outside! I know, I’ll make Christmas lights in Excel!!! Brilliant idea me!!!

What’s the plan?

• Randomly assign green and red to sheet names
• Use the lyrics from “We wish you a Merry Christmas and a Happy New Year” as sheet names!
• Create an off/on switch for the lights
• Different patterns for the random red & green?

In sheet ‘REMOTE CONTROL’ I created this formula in column G:

=IF($A$6=”Lights off!”,ChangeTabColor($E10,255,255,255),
IF($C$6=”Random”,CHOOSE(RANDBETWEEN(1,2),ChangeTabColor($E10,255,0,0),ChangeTabColor($E10,0,100,0)),
IF($C$6=”Rotating”,CHOOSE(ROUND(1+$C$4,0),ChangeTabColor($E10,255,0,0),ChangeTabColor($E10,0,100,0)))))

How Does The Formula Work?

Part 1

=IF($A$6=”Lights off!”,ChangeTabColor($E10,255,255,255),

If you select “Lights off” in cell A6 then the UDF makes all sheets turn white.

Part 2

IF($C$6=”Random”,CHOOSE(RANDBETWEEN(1,2),ChangeTabColor($E10,255,0,0),ChangeTabColor($E10,0,100,0)),

If you select “Random” in cell C6 the RANDBETWEEN function randomly assigns red or green to each sheet listed in column E.

Part 3

IF($C$6=”Rotating”,CHOOSE(ROUND(1+$C$4,0),ChangeTabColor($E10,255,0,0),ChangeTabColor($E10,0,100,0)))))

If you select “Rotating” in cell C6 the UDF uses the RAND() function is cell C4 to make all sheets the same random color.

Sing Along with a SNOWMAN!

In Excel 2016 there are some interesting “Icons” that we can use. On the ribbon select “Insert” and “Icons”. I ended up creating a singing snowman! Press the arrows to cycle through the lyrics and sing along!

Watch My Video!

Download the Excel file!

You can sign along with the snowman while you watch the sheet tabs change colors or you can make your own version! Maybe you’ll add Santa Claus and some elves?! Note that this file has macros.

00171_CHRISTMAS LIGHTS (tabcolor D Hager)2

Happy New Year!

I save time on Christmas tree decorating so that I have more time for Microsoft Excel!

Do you have any interesting ideas? Leave a comment below or send me an email:  myspreadsheetlab at gmail dot com