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