Moving highlight scroll bar

[Klartigue]

Hello,

I use this code built into a sheet to have a moving highlight when I scroll up and down rows. However, can I just place something similar to this at the end of my macro to say on the current worksheet, have a moving highlight when scrolling?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Target.EntireRow.Interior.ColorIndex = 35
End Sub

[Klartigue]

Right now to use that code in a sheet I right click "Sheet 1" and say view code and paste the above code in the macro and that does the trick. However is there a vba macro for the process of right clicking sheet 1, saying view code, and pasting the selected code, so I don't have to do it manually every time? Can this step the last step in a large macro I have?

[Aussiebear]

Try recording a macro, then adjust the code if necessary.

[ashleyuk1984]

That's actually quite neat.

Do it once, save your workbook as a macro workbook, and then the code will be saved. You won't have to insert it everytime.

I presume that your saving your workbook as a normal xlsx book, so therefore the code actually gets wiped every time. Save it as a xlsm and then you'll be fine.

(assuming that your using Excel 2007 onwards)
The same applies in earlier versions, just the file extension is different.

[Klartigue]

I tried recording it and it doesn't work. I don't run this macro on the same workbook everytime, therefore saving this as a macro workbook with the code wont work because its a new workbook everytime.

[Klartigue]

I tried using this code in my macro but it doesn't seem to work. Can we slightly alter it to make it work?

Sub Test()
Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Target.EntireRow.Interior.ColorIndex = 35
End Sub

[snb]

You have to use a workbook event not a worksheet event

In "thisworkbook':

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   sh.Cells.Interior.ColorIndex = xlNone
   Target.EntireRow.Interior.ColorIndex = 35
End Sub

[Klartigue]

Ok so is there anyway to integrate the above macro into my greater macro? I would I call the above to run?

Sub Spread()
Call Spreadsheet
Call PasteValuesinSheet
Call NameEarlyMidLate
Call OpenEarlyMidLateInput
Call CopyInput
Call CloseInputsheet
Call SelectBook1
Call Formula
Call FormulaCallDate
Call Formula2
Call NameColumnsOandP
Call ExpandP
Call Workbook_SheetSelectionChange ????????
End Sub

[SamT]

You don't Call an Event triggered Sub. It runs whenever the Event occurs. You could say "The Event 'Calls' the sub."

In order to have the the Sub respond to Events in all Workbooks, you must write it as an Application Event, and that means creating an Event Class Module.

It might be easier just to put the Workbook Event Sub in all the Workbooks it has to work in. Of course, this wouldn't be a good idea if you want it to work in new workbooks.