Highlight Current Column and Row not working on 2nd worksheet

[lebrocoli]

The following vba code works very well on my first worksheet, but does not work on any other worksheet.

My names are defined with 'Workbook' scope.

I took the vba code assigned to the first worksheet and copied it to the second worksheet, 'Within the VBA Editor', but somehow, it does not work.

Do I need to tweak something?

------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("CurrentRow")
.Name = "CurrentRow"
.RefersToR1C1 = "=" & ActiveCell.Row
End With

With ThisWorkbook.Names("CurrentColumn")
.Name = "CurrentColumn"
.RefersToR1C1 = "=" & ActiveCell.Column
End With
End Sub
--------------------------------------------------------------

 

[jmacleary]

Hi there. You are trying to define the same name twice. Once the code has been executed on the first worksheet, the names exist, if you actually want the same name to be redefined when you select a different sheet you will need to remove the definition first.

 

[DanteAmor]

Try this

Create 2 rules on each sheet where you want the operation



Rule 1
Formula:
=ROW()=CELL("row")

Format:
Select your color

Applies to:
=$1:$1048576


Rule 2
=COLUMN()=CELL("column")

Format:
Select your color

Applies to:
=$1:$1048576


--------------------------

Put the following code in Thisworkbook event

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Calculate
End Sub

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the below code.