Sub Workbook_Open() - to run for ANY wkbk that is opened

[craigexcel]

I found this code that will automatically shade both the row and column of the cell that has the focus. I'd like for this action to happen for ANY wkbk that is opened. Since it seems that would require the use of "'Private Sub Workbook_Open()", is there a way to make this work? I had put this code in the Workbook object of ThisWorkbook.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'automatically shades row AND column of selected cell

Static xRow
Static xColumn

If xColumn <> "" Then
    With Columns(xColumn).Interior
        .ColorIndex = xlNone
        End With
    With Rows(xRow).Interior
        .ColorIndex = xlNone
        End With
End If

pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn

With Columns(pColumn).Interior
    .ColorIndex = 24
    .Pattern = xlSolid
End With
With Rows(pRow).Interior
    .ColorIndex = 38
    .Pattern = xlSolid
End With

End Sub

 

[shg]

That would hose the formatting of any worksheet that has fill, and it will eliminate Excel's Undo functionality, but OK.

Create a new workbook, and put this in the ThisWorkbook module:

Option Explicit

Dim WithEvents app As Application

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ' your code goes here
End Sub

Save as an add-in and install when you want it.

EDIT: Glancing at the code, you'll need to modify it to eliminate the use of the static variables; they would only apply to the last worksheet modified.

 

[craigexcel]

Many thanks, shg. I appreciate your insight!

 

[shg]

You're welcome.