I started on a role to reconcile accounts and it involved manual reconciliation which could not be automated. Thus I use to check 2 sheets with an account number and if amounts match up in the 2 sheets then would have to highlight it with a color. But being keyboard savy it was a pain in excel to use the mouse and choose a color from the toolbar. Rather I wanted a keyboard shortcut which when pressed will highlight any cell and if pressed again will remove the highlight. After learning a little VBA I was able to make my own shortcut. Below is how I did it:
Open the visual basic editor by hitting ALT+F11 from excel. Create a new module (ALT+I+M) and add the below code.
If Selection.Interior.ColorIndex = 6 Then
Selection.Interior.ColorIndex = 0
Selection.Interior.ColorIndex = 6
Now switch back to excel and hit ALT+F8 which should bring the macro dialog box with the macro “markcolor” in the list. Just select it and click on “Options” which prompts to enter an alphabet for shortcut key. I enter the alphabet q in the box. And say Run.
That is it now go back to excel and when you hit CTRL+q the cell gets highlighted with yellow color and if pressed again it removes the color.
1) This shortcut will only work in the excel workbook where you have added the module. For advance users who want to have it available in the entire excel applications irrespective of any file selected, this code should be written in personal.xlsb file.
2) The color code for yellow is 6. If you need any other color then play around by changing this number.
Attaching the file with the code for reference. Happy Learning