Excel & VBA

22 Feb 2018

VBA Button to Color Entire Row

Posted By

The Result

Auto Color Rows

Figure 1: Auto Color Rows

How many milliseconds a year do you spend coloring in cells in Excel? Thousands I’m sure! Whilst Excel does make it easy for you with some handy Fill Color tools in the Home Ribbon, there is a quicker way! Why not set up some code that will allow you to just select a single cell in the row and do the rest for you.

The Code

Below is an example sub-routine that will change the cell and font color of your table. Be sure to edit the following lines to suit your needs:

  • Line 4: Change the number to match the row number of your heading row
  • Line 5: Change the “B” & “E” to match the extents of your table columns.
  • Line 6: The color you want the cell (See more color options here)
  • Line 7: The color you want the font

Now you could copy the above subroutine a number of times for each type of cell/font color you would like. However, this sounds like a great place to get fancy and use a function(Learn More About Functions Here)

Drake Fancy

Figure 2: Drake Fancy Walk

With the power of a function we can just pass the arguments that are we’d like to change. Check it out below!

Feel free to continue building on the function from here! Perhaps you could also get the code to change the status of the row in the last column? Let me know in the comments if you would like some guidance on how to do this.


If you can't convince them, confuse them!

Leave a Reply