Highlight row in other sheets

[mecerrato]

Hi, I am using this great little piece of code that I got from this site to highlight the active row I am on:

Automatically Highlight Active Row in Excel

I use Excel… a LOT, and for a very long time! With that comes the inevitable… my eyes aren’t as young as they used to be… plus, we tend to have a TON of data in our Excel fi…

lifehacks365.com

But I tried applying to other sheets in the workbook and it does not work, the sheets I am trying to apply this to that are contained in the same workbook are "Credit_Pulls", "Applications", "Fundings", and "Current_Pipeline".

Can someone help me tweak to make it work?

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

 

[HaHoBe]

Hi mecerrato,

instead of Worksheet_SelectionChange behind the sheet you should use Workbook_SheetSelectionChange in ThisWorkbook. You should comment the code behind the sheet i where it is located by now and you would need to copy the CF to any sheet you indicated:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Select Case LCase(Sh.Name)
  Case "credit_pulls", "applications", "fundings", "current_pipeline"
    With ThisWorkbook.Names("HighlightRow")
      .Name = "HighlightRow"
      .RefersToR1C1 = "=" & ActiveCell.Row - 4
    End With
    With ThisWorkbook.Names("HighlightRowCredit")
      .Name = "HighlightRowCredit"
      .RefersToR1C1 = "=" & ActiveCell.Row
    End With
  Case Else
End Select
End Sub

Ciao,
Holger

 

[Snake Eyes]

Hi HaHoBe,
I'm trying to accomplish the same fix in my workbook.
Can you explain what you mean by:
...Comment the code behind the sheet...?

Also, do the names of the worksheets need to be in all lower case because of the "Select Case LCase(Sh.Name)" in the VBA Code?

 

[HaHoBe]

Hi Snake Eyes,

if you place a routine in ThisWorkbook this will work for all worksheets so any single routine for teh same reason behind any sheet should be commented.

Yes, all names are lower case due to LCase being used.

HTH,
Holger.