How to FOCUS in Excel part 2

Business & Tech, How To's

Group work. You have to love them!

Just like most projects, good scenarios that might challenge the previous solution in real life come about after the requirements have been workshop’ed by the collective group.

In my prior post where I presented an old solution to quickly spot data in a large data set, I was presented with 2 options to clear the worksheet highlights (you see, the original solution presented left the last cell(s) highlighted even after you cleared the code).

Roberto Mensa pointed out a potential problem and kindly provided a solution.

The Problem

If we insert the original solution to a sheet with formats – such as background colours, border styles etc. – then the 2 solutions we had would clear the predefined formats too.

The Solution

Roberto writes:

if we have a formatted sheet will be deleted everything … I would do this: 
I select the entire sheet or a part, according to the requirements 
add this rule as conditional formatting: 
=AND(CELL(“row”)=ROW(),CELL(“col”)=COLUMN()) 

then this code in worksheet class module: 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
Target.Parent.Calculate 
End Sub 

in this way our cells already formatted will not be ruined 

of course at the end of work will need to delete the rule and code formatting 
regards

r

In addition, Roberto added a sample file: http://goo.gl/U9yXl. (remember to insert the =AND(CELL(“row”)=ROW(),CELL(“col”)=COLUMN()) conditional formatting code)

Now let’s all thank Roberto for the problem scenario and his solution and the good people of the group for brainstorming our problem. Thanks guys!

2 thoughts on “How to FOCUS in Excel part 2

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