How to FOCUS in Excel – the followup

Business & Tech, How To's

We have 1 last follow-up to this post.

Recall, that our last update presented a novel solution to store any formats in the sheet if the ‘Excel clear’ function somehow deleted any formats by accident.

This solution highlights your sheet (in orange, set the property “.Color = 49407”) and clears the format. The developers note:

..It clears format and highlights for analyzing large sets of data. I currently have this set in each sheet I need it.

Dim RowRng As Long
Dim ColRng As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If RowRng <> 0 Then
With Rows(RowRng).Interior ‘begin clearing previous highlights
.Pattern = xlNone
End With
With Columns(ColRng).Interior
.Pattern = xlNone
End With
End If

RowRng = Target.Row
ColRng = Target.Column
With Rows(RowRng).Interior ‘set highlights
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
End With
With Columns(ColRng).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
End With
End Sub

Thanks to Ryan Ashcraft for the above input.

I am amazed at the wealth of people out there who are willing to impart their knowledge and time to this.

Of course a really simple solution to all this is to make a back-up copy of the sheet that you are about to analyse. After completing your analysis, simply delete the sheet with the VBA code and revert back to your back-up.

2 thoughts on “How to FOCUS in Excel – the followup

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