Color a cell by just clicking on it

Last week I got a question from Amit:
I use the asap utilities a lot and it is great help. My question is regarding cell coloring. I'd like to be able to assign only two colors to a cell. For example, say if you select a cell once (or click on it once), it turns Red. If you click on it again, it turns back to no color. This is to avoid having the user select the cell and having to go through the pallet to select a color etc. Can this be done in excel and if so, how? Thank you for your help and keep up the good work.

This can indeed be done in Excel but you need to do some programming.
In this case you could for example run a macro that makes a cell red when you double-click on it, and when you double-click on it again the red color is removed. I think double-clicking is more user-friendly than single-clicking.

make a cell red when you double-click on it, and when you double-click on it again the red color is removed

Private Sub Worksheet_BeforeDoubleClick( _
        ByVal Target As Range, Cancel As Boolean)
' This macro is activated when you doubleclick
' on a cell on a worksheet.
' Purpose: color or decolor the cell when clicked on again
' by default color number 3 is red
      If Target.Interior.ColorIndex = 3 Then
            ' if cell is already red, remove the color:
            Target.Interior.ColorIndex = xlNone
      Else
            ' make the cell red:
            Target.Interior.ColorIndex = 3
      End If
      ' true to cancel the 'editing' mode of a cell:
      Cancel = True
End Sub

You can download an example workbook demonstrating this technique.

One comment

Amith Agarwal

Hi. Super man,

i just came to about ASAP utilites and i am too much intrested and i want to be guru in this so i need one help from u reg the find option for example if the find option satisify 2 conditions its should replace the content pls can u help me on this .
suppose if we have three coloums one and if 2 conditions in 2 coloums are true its should replace the content in 3rd coloum. pls help me on this