How to FOCUS in Excel

Business & Tech, How To's

Right. Time to toggle the brain back to the other side after all that poetry.

Here’s my 2nd installment for Microsoft Excel users.

This is an old trick that will assist Excel users that have to scroll through large amounts of data throughout their working day.

Here’s the problem

You are attempting to find a piece of data in a large data set.

Just say you sheet looks like this:

Without

And the data set goes on to infinity – and then some.

You can either stain your eyes by moving the mouse along the cells OR you can go in to the VBA backdoor and highlight the cells as follows..

The Solution

Option 1: Select a single cell

This code allows you to highlight one cell at a time:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False

‘ Clear the color of all the cells

Cells.Interior.ColorIndex = 0

‘ Highlight the active cell

Target.Interior.ColorIndex = 8

Application.ScreenUpdating = True

End Sub

Note that the ColorIndex = 8 is configurable. If I wanted to highlight my cell grey for example then I would change the ColorIndex = 15. It would then look like this:

With

Option 2: Select multiple cells

Now if you really need to quickly see the column and row references quick smart for that report you had to do yesterday, then there’s this other bit of code that you can use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

‘ Clear the color of all the cells

Cells.Interior.ColorIndex = 0

With Target

‘ Highlight the entire row and column that contain the active cell

.EntireRow.Interior.ColorIndex = 8

.EntireColumn.Interior.ColorIndex = 8

End With

Application.ScreenUpdating = True

End Sub

There is an option 3 too which is a variation of option 2 once again made available here.

Where do I insert the code?

For the non-technical folk, if you want to insert the above code then you will need to open your Developer Ribbon. Here are the instructions.

Assuming that you can see your Developer Ribbon, all you need to do now is copy the code and insert it as follows:

  1. Go to your Developer Ribbon and select Visual Basic
  2. Select the sheet you want to highlight
  3. Insert your code

3 easy steps represented here:

Insert Code

Sample please!

Sure. Here’s an entire sample sheet of the above in practice: Excel User 2 Focus and Clarity.

To view the working model, you will have to enable macros which is under Macro Security (refer to point 1 in the picture above, Macro Security is a few spots beside it).

Now the only thing that bugs me about this code is when you stop highlighting cells and delete the code from the sheet. Excel remembers the highlight function and leaves your last highlighted area highlighted.

To stop that behaviour, a clear-the-colours function is required – expressed here via Piotr Furtek:

Private Sub Worksheet_Deactivate() 
Cells.Interior.ColorIndex = 0 
End Sub

Now the code works exactly the same way as the prior code, which is, insert the code on the sheet after you have finished highlighting and hit Run Macro symbol (or simply hit F5). This clears the highlights.

And yet another method to stop that behaviour is via David Lanman:

Sub Macro1()
Selection.Clear
End Sub

or

Sub Macro1()
Cells.Clear
End Sub

Now these to bits of code will clear your selection and not just the highlighted area.

Hope this was helpful.

Would love to hear your thoughts.


					

3 thoughts on “How to FOCUS in Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s