Here’s a relatively simple way to color a cell in Excel using a double-click instead of the toolbar’s fill option. The trick is to use some VBA code to color a cell when double clicked then clear it if double clicked again. There are various uses for something like this; one example is the DontBreakTheChain.xls spreadsheet (see Seinfeld’s Secret Motivational Technique) which allows users to track their progress by marking off each day they work towards a goal.
A variation of the code used in the DontBreakTheChain.xls spreadsheet is shown below. All you have to do is right-click on the worksheet of your choice and select “View Code”, then copy and paste the code to the sheet’s class module and presto! Sound complicated? It really isn’t, just watch the video tutorial below for an explanation and some extra tips. Hint-Hint, you can use a similar technique to color a cell with a right-click or depending on the current cell color. Want to know how, then watch the video.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
' This subroutine colors a cell red when double-clicked then clears it when double-clicked again.
' Some values for .ColorIndex are...
' Red = 3, Green = 4, Blue = 5, Yellow = 6, Orange = 45
' Google "VBA color palette" for more colors
' If the cell is clear
If Target.Interior.ColorIndex = xlNone Then
' Then change the background color to red
Target.Interior.ColorIndex = 3
' Else if the cell background color is red
ElseIf Target.Interior.ColorIndex = 3 Then
' Then clear the background
Target.Interior.ColorIndex = xlNone
' This is to prevent the cell from being edited when double-clicked
Cancel = True
As always, feel free to leave a comment with any questions or additional tips you might have!
The Closet Entrepreneur
Post Scriptum: For more Excel tips, check out these posts…
10 Excel Tips to Make Your Life Easier – Part 1
10 Excel Tips to Make Your Life Easier – Part 2
How To Add a Vertical Line to an Excel XY Chart