Worksheet Selection Change - Limiting to Specific Worksheet

[sam89665]

Hi,

I currently use a combination of VBA and conditional formatting to highlight the row the cursor is on for a specific worksheet.

VBA Sheet Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub

Conditional Formatting:
=CELL("row")=CELL("row",A1)

What I'm finding annoying is when I make a selection change on a sheet in a different workbook and then go back to this spreadsheet the row is no longer highlighted, due to me changing the selection in the other workbook.

Therefore, my question is, can I amend the VBA code to restrict it to that specific workbook/worksheet only (i.e. so that it ignores selection changes in other workbooks)?

Many thanks!

Sam

 

[rollis13]

Sorry, can't replicate your problem. As per your explanation the highlighting in first workbook doesn't disappear whatever I do in the second workbook.
Please attach a demo workbook with problem.

 

[RoryA]

Your conditional formatting rule appears to be flawed. If you don't include a cell reference in the CELL function, it will be applied to the last altered cell in any open workbook, which is not what you want. I think you'd be better off with a defined name that stores the current row number, and use that in the formula - eg =ROW()=rowNum

 

[sam89665]

Your conditional formatting rule appears to be flawed. If you don't include a cell reference in the CELL function, it will be applied to the last altered cell in any open workbook, which is not what you want. I think you'd be better off with a defined name that stores the current row number, and use that in the formula - eg =ROW()=rowNum

Thanks for the reply. So if I understand correctly, I should define a name as follows:

But how would I adapt the conditional formatting rule to incorporate this? The current rule is:

=CELL("row")=CELL("row",A1)

Appreciate your help, thanks.

 

[RoryA]

No, you'd define the rowNum name using a specific number (eg =1) and then your CF formula is =ROW()=rowNum. Your SelectionChange code updates the rowNum name and then recalculates the sheet.

 

[sam89665]

No, you'd define the rowNum name using a specific number (eg =1) and then your CF formula is =ROW()=rowNum. Your SelectionChange code updates the rowNum name and then recalculates the sheet.

Ok, so I've changed the name to this:

and the CF to this:

But that's not working. The formatting is stuck on Row 1, no matter where else I click. I'm obviously doing something wrong?

Once again, appreciate the help.

 

[RoryA]

I think you missed the part where I said "Your SelectionChange code updates the rowNum name". For example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.Names("rowNum").RefersTo = "=" & Target.Row
Target.Calculate
End Sub

 

[sam89665]

I think you missed the part where I said "Your SelectionChange code updates the rowNum name". For example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.Names("rowNum").RefersTo = "=" & Target.Row
Target.Calculate
End Sub

I did, but wouldn't have known how to do that anyway as I'm new to VBA.

It's working now - so I'll try using that going forwards.

Many thanks for the help, and sorry for my dumb questions.

 

[RoryA]

Glad we could help, and there were no dumb questions there, believe me.