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
Set thisFC = thisRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=ROW()>0")
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)' ?