How to highlight row when click and apply code to all tabs in Excel

Question

I want for a row to be highlighted when clicked and remove the highlight of that row when another one is clicked.

For this, I've found a code here to do it in a particular tab. I what to apply it to all the tabs. Therefore I've added the following code in 'ThisWorkbook':

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Static xRow
    If xRow <> "" Then
        With Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If
    pRow = Selection.Row
    xRow = pRow
    With Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

With this code the row gets highlighted when a value of that row is changed, but not when clicked. Is there any way to achieve to highlight when clicked for all tabs?

You are looking for this event: `Private Sub Worksheet_SelectionChange(ByVal Target As Range)` but this has to go to the sheet you intend your code, you should replicate it for every sheet. — Damian, May 24 '19 at 09:20
I had it like this in one sheet, but is it not possible to have it in 'ThisWorkbook' to apply to all sheets instead of having to copy to all sheets? If the users add new tabs we will have to keep maintaining the file. — Selrac, May 24 '19 at 09:35
As far as I know sheet events must be coded on each sheet. Maybe someone knows better, sorry. — Damian, May 24 '19 at 09:36
You can add code dynamically on sheet creation by using vba extensibility see this link http://www.cpearson.com/excel/vbe.aspx and refer to this https://stackoverflow.com/questions/15082722/adding-code-to-new-excel-sheet-dynamically — Ricardo Diaz, May 24 '19 at 09:52

cekar · Accepted Answer · 2019-05-27T12:42:23.813

This works for me. It uses Worksheet_SelectionChange instead of Worksheet_SheetChange. Only current cell gets highlighted. I've added it into a sheet code window. When you paste the code directly to "ThisWorkbook" code window then it works for all Sheets.

Option Explicit

Dim PreviousCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not PreviousCell Is Nothing Then
        Dim xRow As Variant, prow As Variant

        prow = Selection.Row
        xRow = prow

        If Not PreviousCell Is Nothing Then
            With Rows(PreviousCell.Row).Interior
                .ColorIndex = xlNone
                .Pattern = xlNone
            End With
        End If

        With Rows(xRow).Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        End With

    End If

    Set PreviousCell = Target
End Sub

To paste the code into ThisWorkbook select the object at top above the code window > choose "Workbook" and then select the procedure > select "SheetSelectionChange". Now copy/paste the code between Private ..... End sub

When you click inside the Sub and it look like this ( (General) ) it's not working:

This is working:

This will not work:

Idea came from this answer: Excel VBA: Get range of previous cell after calling LostFocus()

I tried to use your code in 'thisWorkbook' but it didn't work. It works if added in each of the sheets individually. Did you do anything else to make it work in 'thisWorkbook' for all the sheets? — Selrac, May 27 '19 at 11:54
Ok, I think it should not work because you pasted the Code without setting the object. Please follow my edited answer — cekar, May 27 '19 at 12:43

How to highlight row when click and apply code to all tabs in Excel

1 Answers1