How To Color a Cell With a Double-Click In Excel
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
End If
' This is to prevent the cell from being edited when double-clicked
Cancel = True
End Sub
As always, feel free to leave a comment with any questions or additional tips you might have!
Sincerely,
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
» This entry was filed under Excel and tagged with: Excel, Productivity, tutorial, video
11 Comments
Dan
Thanks for the great instructional vid. I thought I’d forgotten all my VB, but this brought it all back.
~Dan
Dec 17th, 2007
TOMAS
Glad you enjoyed the video Dan! As for forgetting all you VB, I’m just amazed at how much VB I have yet to learn and use - my goal is to one day not have to revert to recording a macro every time I need to streamline a process.
Dec 17th, 2007
Daniel
This post is interesting…But, what if I want to color the cells randomly? I mean the color is not always red, but also pink, yellow, etc. Thank you previously.
Feb 1st, 2008
TOMAS
@Daniel: To randomly color cells whenever they are double-clicked, use the following code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Interior.ColorIndex = Int(Rnd() * 55)
Cancel = True
End Sub
Check out the video (if you haven’t done so already) to see where the code should be inserted. Hope that works!
Feb 17th, 2008
Kevin
When I lock a sheet it gives me a debug error. Am i doing something wrong. It works fine until I lock it.
Apr 13th, 2008
TOMAS
@Kevin: I believe the reason you get a debug error is because once a sheet is protected (locked), the sheet and all cells become read-only and modifying any aspect of it (in this case, coloring the cell) is not allowed until you remove the protection. Also, that’s why the debug error shows up because the code gets executed up to the point where the cell color is changed. Hope that makes sense.
Apr 14th, 2008
Kevin
Thanks for your response. That makes sense. Do you think there is a way around it or do I have to leave the sheet unprotected.
Apr 14th, 2008
TOMAS
@Kevin - There is a way around the issue, it involves modifying the macro to uprotect the sheet, make the change you want, then protect the sheet after the changes are made. The issue is that you will have to reveal your password in the macro code, so any knowledgeable Excel user will be able to get the password and unprotect the sheet if they want to. Here’s an example of how to do this:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:=”yourpassword”
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 3
ElseIf Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = xlNone
End If
Cancel = True
ActiveSheet.Protect Password:=”yourpassword”
End Sub
Apr 15th, 2008
Mark
That really was a great tutorial.
I love excel, but apparently am a newbie as I never knew about this view code stuff before.
I do have a beginner type of question after seeing this.
When I double click a cell, instead of turning it a certain color, how do I set it to insert a certain character(s) instead?
Thanks
Jul 23rd, 2008
TOMAS
@Mark - The easiest way to pull something like this off is to use the following code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Target.Value = “hello”
Cancel = True
End Sub
When you double click a cell, it will insert hello in the cell. To change the inserted value to a number or other character(s), simply enter the preferred value in place of hello in the double quotes. Hope this helps!
Jul 23rd, 2008
Rob
Great stuff..been looking for some info like this…. question:
how do i get the cell to change color when double clicked but in a range of cells not the whole page?
thanks.
Jun 12th, 2009
Leave a Comment