How To Color a Cell With a Double-Click In Excel

Double-Click Invaders

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 Levitra sale DontBreakTheChain.xls spreadsheet is shown below. All you have to do is right-click on the worksheet of your choice and Zovirax 800mg 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 Prices viagra generic background
        Target.Interior.ColorIndex = xlNone
 
    End If
 
    ' This is to prevent the cell from being edited when double-clicked
    Cancel = True
 
End Sub

(Subscribers – please visit the site to view the video)

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: , , ,

33 Comments

  1. Dan

    Thanks for the great instructional vid. I thought I’d forgotten all my VB, but this brought it all back.

    ~Dan

  2. 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. :D

  3. This post is interesting…But, what if I want to color the cells randomly? I mean the Flagyl 400mg color is not always red, but also pink, yellow, etc. Thank you previously.

  4. 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!

  5. Kevin

    When I lock a sheet it gives me a debug error. Am i doing something wrong. It works fine until I lock it.

  6. 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 Buy xenical online canadian modifying any aspect of it (in this case, coloring the cell) is not allowed until you remove the protection. Also, that’s why the Buy generic orlistat debug error shows up because the code gets executed up to the point where the cell color is changed. Hope that makes sense.

  7. 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.

  8. TOMAS

    @Kevin – There is a way around the issue, it involves modifying the macro to uprotect the Cipro zithromax no prescription 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

  9. 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

  10. 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!

  11. 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.

  12. @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. ;)

  13. 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 Zithromax without prescription View Code option. Any way to do this on Excel 08?

  14. @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

  15. 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

  16. sean

    Ok thanks, don’t see how I missed that :s

  17. 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!!!

  18. 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

  19. @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.

  20. 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 Viagra Online Without a Prescription function of this VBA code in Excel 2007?

    Many thanks for any assistance :)

  21. 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 Buy zithromax without a prescription 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

  22. 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

  23. 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

  24. 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

  25. Steve

    Thanks for this, appreciate it!

  26. 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

  27. Greg

    Thanks for the code, Tomas. It’s come in quite handy. Simple, neat and elegant.

  28. 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

  29. 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.

  30. 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

  31. 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

  32. 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

Leave a Comment

*
Please type the answer to the math equation. Click the pic to hear the numbers.
Click to hear an audio file of the anti-spam equation