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”.
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/are the macro(s) ?
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 Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...