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
35 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
TOMAS
@Rob – You can try using the intersect method to perform an action if the active cell falls within a defined range, like this:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Intersect(ActiveCell, Range(“A1:B10”)) Is Nothing Then
‘Do nothing
Else
‘Perform following when cell is found in range
‘ 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 If
End Sub
I hope this helps. 😉
Jul 15th, 2009
Kelly Wilson
This was wonderful….exactly what I needed to make the data sheet I desire. One question though, on my Mac Excel 08, I do not have the View Code option. Any way to do this on Excel 08?
Aug 9th, 2009
TOMAS
@Kelly Wilson – Unfortunately, the latest version of Excel for Mac no longer featured Visual Basic for Applications, so this won’t work on your version of Excel. Microsoft has encouraged Mac users to begin using AppleScript to create their macros in Excel, but I haven’t taken the time to learn it just yet. I’ll be sure to post a solution for Mac Excel 08 if I ever get around to learning how to do this in AppleScript.
BTW, for more info about this subject, see the comment I left on another one of my blog posts:
http://theclosetentrepreneur.com/export-excel-data-to-google-maps#comment-12114
Aug 10th, 2009
sean
Hi,
great tip, I’ve never used VB before and I just start to see the possibilities!
One question: how would you go about if you would only like to apply this to a certain range of cells (say a rectangle from B10 to C20) instead of to the whole sheet?
Cheers
Aug 11th, 2009
TOMAS
@sean – You’re in luck! 😉
http://theclosetentrepreneur.com/how-to-color-a-cell-with-a-double-click-in-excel#comment-12718
Aug 11th, 2009
sean
Ok thanks, don’t see how I missed that :s
Aug 12th, 2009
Seth
Thank you so much for this. Can you take it one step further and make it so the first time you double click it changes to one specified color, then on second double click it changes to a second specified color, and on third double click goes back to its orginal state? PLEASE??
Thank you!!!
Sep 12th, 2009
Sim
Hi
Thanks for the great info.
Is it possible to to have different ranges with different colours. For example if I double click on cells A1:A10 they turn red and then I double click on cells B1:B10 they turn green?
Thanks
Sep 23rd, 2009
TOMAS
@Seth – Sure, just add an additional Else/Then statement that goes to your second color as shown below (you still need the full code that goes before and after the statments):
…
‘ 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 change the background color to blue
Target.Interior.ColorIndex = 5
‘ Else if the cell background color is blue
ElseIf Target.Interior.ColorIndex = 5 Then
‘ Then clear the background
Target.Interior.ColorIndex = xlNone
…
@Sim – To get different ranges with different colors, just use the intersect method that I outlined in this comment above and add a second “If Intersect…” section with a different range and color choice.
Oct 19th, 2009
Kat
I hope this isn’t a stupid question…
I just used this code in a Excel 2003 and it worked great!
But then I switched to Excel 2007 and the code is not working in the same file. The sheet is not locked, and I can access the code (ie. function was installed), but it’s not working.
Is it possible I have something switched on/off disabling the function of this VBA code in Excel 2007?
Many thanks for any assistance 🙂
Mar 14th, 2010
Dennis
Tomas,
Re: How To Color a Cell With a Double-Click In Excel, 11-06-2007
This does not work for me in either Excel 2003 or 2007. First of all, when I right-click in a worksheet, there is no “View Code†option. So I open the VB editor with Alt-F11, insert a Module, and paste in your code. When I go back to the worksheet and go to Tools, Macros, nothing is in the Macro dialog box. The box prompts me to enter the name of the Macro, which I do: Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean). But the Run button is not active. When I try running the routine from the VB editor, I get the same empty Macro dialog box. I’ve also tried double-clicking in cells on the worksheet, thinking this might activate the macro, but nothing happens. The workbook is macro enabled, so that’s not the problem. What am I doing wrong? This is a good idea that I could use, so any help is greatly appreciated.
Dennis
Mar 9th, 2011
Dennis
Tomas,
Re: How to Color a Cell With a Double-Click In Excel
I sent a question earlier today and since then I discovered where I was going wrong. I’m a neophyte to VBA for Excel and I did not understand the basics of where to put the code for BeforeDoubleClick events. I think I’ve got that part straightened out but, nevertheless, I am getting an error message when I double click a cell. The message is this:
Run-time error ’1004’:
Application-defined or object-defined error
When I click Debug, the following line of the code is highlighted:
Target.Interior.ColorIndex = 3
I am using Excel 2007. Thanks for any help you can give.
Dennis
Mar 9th, 2011
Jared Newnam
Hey Dan,
I thought this was a great tutorial. I customized it to my needs. There’s one question I have for you. I’m trying to avoid a specific range of cells from changing color when it’s double-clicked.
Can you provide some insight into this?
-Jared
Aug 13th, 2011
Nawabi
very good micro for change cell color but i want change the text color with double click in excel. do you know?
if you know please teach me.
i’ll wait your answer.
Nawabi
Sep 5th, 2011
Steve
Thanks for this, appreciate it!
Nov 4th, 2011
Joe
This double clicking code works perfect for what I want. Is there a way to have this routine applied to every sheet in a workbook without manually adding it to each sheet? For example I have a “master” sheet that is formatted with all the code and conditions I want. Then the user simply adds a new sheet and copies the master over to the new sheet. The routines however do not copy with it.
Nawabi:
To change the font color use “Target.Font.ColorIndex = x” where x is your color (below you can see where I added in):
‘ Then change the background color to red
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 1
Jan 8th, 2012
Greg
Thanks for the code, Tomas. It’s come in quite handy. Simple, neat and elegant.
Feb 8th, 2012
Maria
Dan,
I’ve been trying to figure out how to do this with a number. I have a chart of four columns, and want to double click cell in colum A for the value “1”, colum B for the value “2”, colum C for the value “3” and column D for the value “4”. I would like for the value to clear when i double click again. When double clicking and getting the value, I want the value to automatically display in colum G, while if I double click it clear in the previous columns, i would like for it to clear in column G as well.
Can you teach me the code for this?
Been trying to find this for a while now!
Thanks,
Maria
Feb 15th, 2012
Dan
Tomas,
I have been using your code and changing this and that to learn but when I copy and paste your code for a select range of cells. It pops up with a Compile Error: Expected: list seperator or ) in the following line. With the colon highlighted.
If Intersect(ActiveCell, Range(“A1:B10″)) Is Nothing Then
I am not very good on VB or even syntax errors in excell formulas either.
Thanks for your help.
Feb 29th, 2012
Kara
The way you have explained this made it so easy for a newbie like me!
I was wondering if you have another code that can change the colour for certain cells for example i need c7 to ak7 when double clicked to be blue but i want the rest red still. Any help with that would be great
May 8th, 2012
Ed
Thank you for the very informative tutorial, that really helped.
I wonder if you could help me further with 2 things?
a) Can you limit the range for where the double click will be effective? Say From A1 to C3 for example? Therefore the the rest of the sheet wouldnt be able to change colour?
b) can you have a reference cell that would score the cell based on its colour? Say I double click A1 until it is Red, scoring say 3, can I display in A5 that the response is a 3…. then if I double click A1 again and it is amber then A5 changes to 2 and Green = 1…. So I could score a RAG status?!
Thanks
Jul 18th, 2012
Alex
Thanks for the great tutrorial!!
How would i make this work for cells that already contain colours though?
Any help appreciate.
All the best
May 14th, 2014
Marie
Marie
I have never used VB before and did not know about right clicking on the tab to view code.
It worked perfectly. I started using my MacBook and got all confused.
After a little browsing I found out that Mac doesn’t support VB.
I knew there was a reason I have kept my Samsung laptop.
It all worked a treat, I was amazed. I now have an assessment sheet that can be colour coded in clicks.
I thought your instructions extremely well organised and easy to follow. I am looking forward to exploring more of your help and advice.
Thank you so much.
Jan 27th, 2015
Bruno
Hi,
I’m stuck with the same problem as Joe… Any help?
“Joe
This double clicking code works perfect for what I want. Is there a way to have this routine applied to every sheet in a workbook without manually adding it to each sheet? For example I have a “master†sheet that is formatted with all the code and conditions I want. Then the user simply adds a new sheet and copies the master over to the new sheet. The routines however do not copy with it.”
Apr 1st, 2015
Leave a Comment