Shortcut to Highlight Cells

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.

Sub markcolor()
If Selection.Interior.ColorIndex = 6 Then
Selection.Interior.ColorIndex = 0
Else
Selection.Interior.ColorIndex = 6
End If
End Sub

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.

Note:

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

 

 

9 thoughts on “Shortcut to Highlight Cells

  1. Iˇ¦m now not sure the place you are getting your info, but good topic. I needs to spend a while learning more or working out more. Thank you for great info I was searching for this info for my mission.

  2. Hiya, I’m really glad I’ve found this information. Today bloggers publish just about gossips and web and this is actually frustrating. A good blog with exciting content, that is what I need. Thank you for keeping this website, I’ll be visiting it. Do you do newsletters? Cant find it.

  3. Hey There. I found your blog using msn. This is a very well written article. I will make sure to bookmark it and return to read more of your useful info. Thanks for the post. I will certainly return.

  4. One other thing I would like to state is that as an alternative to trying to fit all your online degree classes on days of the week that you end work as most people are exhausted when they go back home, try to arrange most of your instructional classes on the week-ends and only a couple courses in weekdays, even if it means taking some time off your weekend break. This pays off because on the weekends, you will be a lot more rested and also concentrated on school work. Thanks a lot for the different tips I have learned from your blog site.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>