×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# an update to vba routine for highlighting the current cell3

## an update to vba routine for highlighting the current cell

(OP)
In the previous thread below, I had posted a vba routine to highlight the current cell.
thread770-485560: general purpose VBA routines that you find useful ?

Recently I copied that vba code into a new spreadsheet and whenever I changed cell (activating the macro) vba threw a type mismatch error at the line "Set mySheet = Target.Parent"
Target was a range, so what's the problem?

The problem was a previous declaration Dim mySheet As Sheet1. I had been under the impression that Sheet1 was some kind of generic declaration type for a sheet. It worked fine for me in the past, and I never thought much about the 1 at the end.

Poking around in my locals window after the error occurred revealed that the "type" of Target.Parent was "object / sheet6". Initial reaction WTF is a sheet 6 type.

Then it dawns on me (duh) every sheet has a tab name you assign but it also has a number that excel assigned to the tabs originally (Sheet1, Sheet2, Sheet3) before you renamed them. It's easy to forget about that number because you only routinely see the tab name. But that original sheet number apparently remains relevant to vba long after you change the tab name. And you can see both designations for each sheet if you look in the project explorer panel within the vba window.

Finally understanding my previous gross conceptual error, my first impulse to correct the problem was to change that declaration to Dim mySheet As Sheet6. And indeed that worked, but that approach would potentially require a customization for every sheet you copy this code into. A better way is to just let it be a variant and everything works fine. I could probably delete the declaration altogether, but even without option explicit that seems like bad programming practice, and also I wanted to remind my future self about this lesson I learned. So I just changed it to Dim mySheet As Variant and now it works fine in any sheet number without customization.

If anyone is interested, here is the new code.

#### Quote (corrected highlighting routine)

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Current Cell row/column highlighter
' update 10/26/22 - change the sheet declaration to variant
' previous update 072121 - uses conditional formatting, and "union"
' Usage note - if for some reason a cell remains highlighted
' ... which is not the current cell, then fix it by
' ... clicking on that cell and then clicking on any other cell
' may have to adjust Sheet1 -> SheetN to match whatever is shown on LHS of project explorer for this sheet (ex sheet3)

Static lastRow, lastCol ' Holds the cell coordinates from last call to sub

Dim mySheet As Variant ' Needs to be variant or else customized to sheet1, sheet2 etc
Dim thisRange, lastRange As Range
Dim thisFC, lastFC As FormatCondition
Set mySheet = Target.Parent

' Clear highlighting from last (previous) cell from last call to this function:
If lastRow <> "" Then ' don't proceed if empty values (when initially open workbook)
Set lastRange = Union(mySheet.Rows(lastRow), mySheet.Columns(lastCol)) ' builds a range that highlights both cell and column of last cell
With lastRange
For Each lastFC In .FormatConditions
If lastFC.Type = xlExpression And (lastFC.Formula1 = "=ROW()>0") Then
lastFC.Delete
End If
Next lastFC
End With
End If

' Highlight current cell:
Set thisRange = Union(mySheet.Rows(Target.Row), mySheet.Columns(Target.Column)) ' builds a range that highlights both cell and column of current cell
With thisFC
.SetFirstPriority
With .Interior
.Color = vbYellow
.Pattern = xlSolid
End With
End With

' Save lastRow and lastCol for the next call...
lastRow = Target.Row
lastCol = Target.Column

End Sub

I'll bet many of you would've figured that error out in a heartbeat. But I'm embarassed to say it took me a few hours over several days to figure out what was going on with that type error until the breakthrough when I finally looked in locals to see the actual data type associated with the parent of the variable target. So I'm posting it here just in case anyone else is using my previous code (and is also as dumb as me about the excel object model) then I'll save you the trouble. And for those who would've figured it out in a heartbeat, you are free to have a chuckle at my expense.

=====================================
(2B)+(2B)' ?

### RE: an update to vba routine for highlighting the current cell

#### Quote:

But that original sheet number apparently remains relevant to vba long after you change the tab name

The Worksheet object has 3 properties that identifies a specific Worksheet:

Index-The value that represents the index number of the object within the Worksheet Collection. Can be changed by moving the Worksheet relative to other Worksheets, manually or via VBA
Name-The visible Tab Name, can be changed manually or via VBA
CodeName-The value that you see to the right of (Name) in the Properties window is the CodeName of the Worksheet. At design time, you can change the CodeName by changing this value manually. You cannot programmatically change this property via VBA.

So, the original Worksheet Name (Sheet1, Sheet2...) is just the text that a person can change and is lost forever when a Worksheet Name property is changed.

If the Worksheet having Name Sheet1 is moved, for instance from index position 1 to index position 2, the Worksheet Name remains Sheet1, but the Worksheet Index changes from 1 to 2 and obviously, Worksheet Name Sheet2 would then have Worksheet Index of 1.

In both of the two instances of change above, the Worksheet CodeName would remain unchanged.

The correction to your original code would have been...

#### CODE

Dim mySheet As Sheet1Worksheet

Your original code Set MySheet to a specific instance of the Worksheet Object.

The Variant type is like a wildcard.

Like your hat-tip to The Bard of Avon.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: an update to vba routine for highlighting the current cell

(OP)

#### Quote (Skip)

The correction to your original code would have been...

#### CODE

Dim mySheet As Sheet1Worksheet
Yes, much better. I knew variant was a way to let excel figure out the type for me, but it relies on excel making the right decision, which sort of defeats the purpose of a type declaration. I think worksheet will do what I was originally thinking sheet1 would do. (I'll try it out when I have access to excel again tomorrow).

I knew the specifics of the excel object model better once upon a time, but I guess I'm getting older and it has grown a bit fuzzy after not mucking around with vba for awhile. I suddenly got dragged back into it when this macro didn't work for me at the worst possible time (when I really needed it to work).

#### Quote:

Like your hat-tip to The Bard of Avon.
Thanks. It's quite the name drop for classic literature buffs, but I can't claim to be one (I'm not that sophisticated). I'm familiar enough with the works of George Boole though.

=====================================
(2B)+(2B)' ?

### RE: an update to vba routine for highlighting the current cell

Well we all have areas of interest that grab our attention. I'm positive, as the proton bragged to the surrounding otherwise endowed trons, that I miss many, perhaps most of the signals surrounding me. But I occasionally discover a few that are like tasty morsels upon which moments of rumination delight. And yours was one!
I was fortunate to have met a guy, almost 3 decades ago, who had worked at Microsoft with Excel VBA. At Vought Aircraft in 1993, I had Excel 5.0 and was trying to replicate in a spreadsheet what I had designed on the mainframe: to display in gantt chart form the manufacturing times for each bill of material item in order to visually understand the long pole in the tent from material purchase, fabrication component assembly and final assembly.
Anyhow, this guy helped me greatly to understand how to use VBA and there were many others along the way.
I don't remember studying boolean logic in college back in the early 60s but I do remember circa 1968 at Grumman taking a course in gate logic, so George got me.

One other rather important fact about variable declarations: A declaration without a data type
(As Dim a, b As Long)
is Assumed (As a) to be a Variant. EACH DECLARATION MUST HAVE AN EXPLICIT As DATA TYPE Assigned.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: an update to vba routine for highlighting the current cell

Further to Skips comment, I went through and added types for each variable, including the two with a Static declaration. I also updated a few of the commets, and indented the loops (which, in my opinion, makes the code much more readable, and is especially a good idea if you ever want to convert VBA to Python, which requires loops to be indented).

- If you are concerned about getting all the right types for your variables, it is a good idea to step through the code with the "Locals" window displayed. That shows the type for each variable, and its value (as soon as it has one).
- You are too hard on yourself regarding your coding skills Pete. I wouldn't have even known where to start with this routine!

#### CODE --> basic

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Current Cell row/column highlighter
' update 10/26/22 - change the sheet declaration to worksheet
' previous update 072121 - uses conditional formatting, and "union"
' Usage note - if for some reason a cell remains highlighted
' ... which is not the current cell, then fix it by
' ... clicking on that cell and then clicking on any other cell

Static lastRow As Long, lastCol As Long ' Holds the cell coordinates from last call to sub

Dim mySheet As Worksheet ' Needs to be Worksheet
Dim thisRange  As Range, lastRange As Range
Dim thisFC  As FormatCondition, lastFC As FormatCondition
Set mySheet = Target.Parent

' Clear highlighting from last (previous) cell from last call to this function:
If lastRow <> 0 Then ' don't proceed if empty values (when initially open workbook)
Set lastRange = Union(mySheet.Rows(lastRow), mySheet.Columns(lastCol)) ' builds a range that highlights both cell and column of last cell
With lastRange
For Each lastFC In .FormatConditions
If lastFC.Type = xlExpression And (lastFC.Formula1 = "=ROW()>0") Then
lastFC.Delete
End If
Next lastFC
End With
End If

' Highlight current cell:
Set thisRange = Union(mySheet.Rows(Target.Row), mySheet.Columns(Target.Column)) ' builds a range that highlights both cell and column of current cell
With thisFC
.SetFirstPriority
With .Interior
.Color = vbYellow
.Pattern = xlSolid
End With
End With

' Save lastRow and lastCol for the next call...
lastRow = Target.Row
lastCol = Target.Column

End Sub 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: an update to vba routine for highlighting the current cell

I should have checked properly before posting!

Having declared LastRow as a long, the line where it is checked needs to be changed to:

If lastRow <> 0 Then ' don't proceed if empty values (when initially open workbook)

(Posted code now edited)

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: an update to vba routine for highlighting the current cell

Appreciating this discussion fellas. I ran into a similar issue as OP and it took me some time to wrap my little head around. Thanks for spreading some knowledge.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!