Can a macro be written to add a a code to a worksheet?

[SueKi]

Hi! I found this really neat trick on ablebits.com to add conditional formatting to highlight the column and row that is currently active. It makes a nice easy visual on big spreadsheets, which is where I live. They have 2 version, one VBA, which erases formatting already there (no good) and one is with conditional formatting. The conditional formatting means you have to hit F9 every time you move to another cell, which is too cumbersome, but they provide code you can add to the worksheet to refresh at every move, which works awesome. I don't want to have to set up every worksheet I use though, as I'm constantly pulling new data files. I need a macro. I think I can so the conditional formatting part, and they provide the code of the worksheet itself, but I need to now how to write code that will put the code on the worksheet. Can that be done?

How to highlight active row and column in Excel

3 ways to dynamically highlight the row and/or column of a selected cell in Excel.

www.ablebits.com

 

[John_w]

You don't need to programmatically add code to each worksheet module where you want to handle the Worksheet_SelectionChange event shown on your link. Instead, use the Workbook_SheetSelectionChange event in the ThisWorkbook module:

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

End Sub

 

[SueKi]

You don't need to programmatically add code to each worksheet module where you want to handle the Worksheet_SelectionChange event shown on your link. Instead, use the Workbook_SheetSelectionChange event in the ThisWorkbook module:

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

End Sub

Hi John, Thanks for responding, but I'm sorry, I don't understand. Is this solution something that I could attach to my shortcuts ribbon and use on any workbook I open? It sounds workbook specific - but honestly I'm not understanding. I can write some basic macros with a lot of recorder use, but am most definitely still newbie status with programming.

 

[John_w]

I think I misunderstood your OP - you want the current row and column highlighting to work in every workbook, not just a specific workbook.

You can have ordinary macros available to every workbook by storing the code in the Personal.xlsb workbook:

Create and save all your macros in a single workbook - Microsoft Support

Create and save all your macros in a single workbook by creating them in Personal.xlsb, a hidden workbook that opens every time you open Excel. Macros created in Personal.xlsb are available every time you open a workbook in Excel.

support.microsoft.com

However, having worksheet and/or workbook event code available in Personal.xlsb, and therefore available to all workbooks, is more involved. One solution I found is here:

Code is working in ThisWorkbook but not in Personal.xlsb

Both shift key on my laptop gets stuck a lot, I usually edit group sheets by accident in Excel when I navigate using ctrl+page up/down. A friend made me a code which I can paste on ThisWorkbook in...

stackoverflow.com