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

 

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.

 

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