Using Worksheet_SelectionChange() event macro

**maacmaac** · 09-27-2007, 04:46 PM

I saw following link regarding highlighting rows while in an excel spreadsheet.

http://www.mcgimpsey.com/excel/highl...iteRetainColor

It indicates to use a Worksheet_SelectionChange() event macro. Here is the code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        Cells.Interior.ColorIndex = xlColorIndexNone
        ActiveCell.EntireRow.Interior.ColorIndex = 36
    End Sub

I can’t figure out how to use the code and I can’t figure out how to turn the code “on” to use it. Any help is appreciated as I am new to event macros.

P.S. once I figure out how to turn it “on”, how do I then turn it “off”?

**VBA Noob** · 09-27-2007, 04:51 PM

Right click sheet > Select view code > Paste in code

So if your in sheet 1 it will hightlight the activecell row only for that sheet

http://www.contextures.com/xlvba01.html#Worksheet

VBA Noob

**maacmaac** · 09-27-2007, 05:16 PM

Thanks for the link. I was able to turn it "on". Is there a way to turn this feature "off" without having to delete it in VBA?

One more question. Sorry to be a pain. If I have multiple workbooks open and each has multiple sheets, Can I turn on this feature for all spreadsheets that are open?

**VBA Noob** · 09-27-2007, 05:32 PM

Not ideal but use a cell to toggle. If A1 says off it stops (note you may have to double click cell A1 to remove format) then type on in A1 to start

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Range("A1").Value = "off" Then
        Cells.Interior.ColorIndex = xlColorIndexNone
        ActiveCell.EntireRow.Interior.ColorIndex = 36
    Else
        ActiveCell.EntireRow.Interior.ColorIndex = xlNone
    End If

End Sub

Code only works by sheet so all sheets need the code. You could try putting in thisworkbook (see link)

Which starts when you open