I’m sure you’re no stranger to an Excel trackers. How many milliseconds a year do you spend coloring in cells in Excel designating status changes? Far too many, I’m sure! Whilst Excel does make it somewhat easy for you with some handy tools in the Home Ribbon, there is a quicker way!

Why not reclaim some of this lost time and use VBA instead? With a few quick tweaks, the code here will let you select a single cell and then color the entire row with a single button click.

Let’s take a look at my tracker below (most important actions at the top, of course) .

Workbook Tracker: Important Things to Do

As you can see, we have some fairly important things to get done. Albeit, on a pretty bland looking tracker. Adding some color will certainly improve this.

Sub-routine: Color Row Green

In our tracker shown above, we can see our good friend Albert has completed his assignments. First let’s start by looking at how we can color this row green to signify this completion.

You’ll likley have to change the following lines to suit your needs.

  • Line 7: My tracker starts in Column A & ends in Column E. Change these accordingly to fit your spreadsheet.
  • Line 8: This uses RGB to turn the cell background green (you can choose whichever color you prefer).
  • Line 9: I also change the font color to a dark green. If you prefer not to change the font color, just delete this line.

If you wish you choose your own RGB color values, there are lots of free tools available online. I often use this one > HTML Color Picker.

Now that we have some code, let’s paste it into the Visual Basic Editor (ALT + F11) & then assign the macro to a shape object on our worksheet. The image below shows your the final effect!

VBA Demo: Are we done yet?

Function: Color Row Whatever Color You Want

Now you could copy the above subroutine a number of times for each cell/font color you need. However this would lead to code-repetition. Therefore, this sounds like a great place to get fancy and use a function! (full lesson about Functions coming soon)

There’s no need to show-off sir…

With the power of a function we can just pass the arguments (colors) that we’d like to change. As a result, we’ll have much cleaner code that’s easier to update.

As you can see it’s now very easy to add code for new buttons (they’re just one line!). Each holds the RGB values we need, and calls the Fancy_ColorRows() function. The example code shown is for the DONE & WIP buttons which are demonstrated below.

Note: I have had to include an RGB array to pass the correct color values (3 individual numbers instead of 1 string), but that’s a lesson for another time.

VBA Demo: Color Rows Buttons
VBA Demo: Well color me impressed!

Function: Change Color & Set Status to Match

Don’t just stop there! Yes, we’ve now got an epic way to visually represent action progress on our tracker. However, we can always improve! Notice how we have a Status column in our tracker. Maybe we also want to set the status to match the color?

Luckily, we can do this fairly easily by adding 1 more argument to the Function & a line of code to execute.

VBA Demo: Go forth and make colorful trackers

If you can’t convince them, confuse them!

— Unknown