Double click or right click to change the color of a cell using a macro in an excel report

It is always useful to put colors into the cell so I can distinguish quickly which, for instance, project is more important than the others. This solution is better than the “conditional formatting” option because I have to decide. In the other hand, if the project is classified, for instance, category A, category B, etc., it is better to use the “conditional formatting”.

formula excel formula excel


When I use the macro ?

To highlight important cells. If you prefer to use the “conditional formatting”, read Highlight a cell with a color with the conditional formatting option with or without a formula in an excel report.


How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report


How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report


How to use the macro ?

No way to use a customized name, it should be “Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)” and it is mandatory to put it inside the sheet you want to do it.

formula excel


How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ' if you prefer right click, change BeforeDoubleClick by BeforeRightClick
' change the color if necesarry
Select Case (ActiveCell.Interior.ColorIndex)
Case 0: ActiveCell.Interior.ColorIndex = 4 ' white 0 to green 4
Case 4: ActiveCell.Interior.ColorIndex = 6 ' green 4 to yellow 6
Case 6: ActiveCell.Interior.ColorIndex = 3 ' yellow 6 to red 3
Case 3: ActiveCell.Interior.ColorIndex = 0 ' red 3 to white 0
' dont modify if not, not working
Case Else: ActiveCell.Interior.ColorIndex = 4
End Select
End Sub

Interesting Management