Adding a parameter to a worksheet event

Question

I have found a macro that works really well to highlight the entire row that corresponds to a selected cell (whatever cell you select, the macro runs, and all rows are highlighted). There are some drawbacks to this current iteration and I cannot seem to find a way to enact parameters I would want to limit the highlighting to a specific number of rows. Any ideas?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Const cnNUMCOLS As Long = 512 Const cnHIGHLIGHTCOLOR As Long = 6 'default lt. yellow Static rOld As Range Static nColorIndices(1 To cnNUMCOLS) As Long Dim i As Long If Not rOld Is Nothing Then 'Restore color indices With rOld.Cells If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore For i = 1 To cnNUMCOLS .Item(i).Interior.ColorIndex = nColorIndices(i) Next i End With End If Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS) With rOld For i = 1 To cnNUMCOLS nColorIndices(i) = .Item(i).Interior.ColorIndex Next i .Interior.ColorIndex = cnHIGHLIGHTCOLOR End With End Sub

It would help to explain what you need to use the parameter for - typically event procedures don't have parameters other than the "built-in" ones. — Tim Williams
– Tim Williams, Commented Jul 9, 2015 at 20:58
I would generally just like for the highlighting to only occur in rows 11:54, however it is proving tedious to enact this. — user3794203
– user3794203, Commented Jul 10, 2015 at 19:21

Tim Williams · Accepted Answer · 2015-07-10 20:17:18Z

Try this out:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Const cnNUMCOLS As Long = 512 Const cnHIGHLIGHTCOLOR As Long = 6 'default lt. yellow Static rOld As Range Static nColorIndices(1 To cnNUMCOLS) As Long Dim i As Long, bClear as Boolean, bInRange as Boolean bInRange = Not Application.Intersect(Me.Range("11:54"), Target) Is Nothing bClear = Not Application.Intersect(Me.Range("A6"), Target) Is Nothing 'exit if selection is not in the first ten rows or A6 If Not (bClear Or bInRange) Then Exit Sub If Not rOld Is Nothing Then 'Restore color indices With rOld.Cells If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore For i = 1 To cnNUMCOLS .Item(i).Interior.ColorIndex = nColorIndices(i) Next i End With End If If Not bInRange Then Set rOld = Nothing Exit Sub ' Exit if we're in A6 End If Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS) With rOld For i = 1 To cnNUMCOLS nColorIndices(i) = .Item(i).Interior.ColorIndex Next i .Interior.ColorIndex = cnHIGHLIGHTCOLOR End With End Sub

Note: your code is assuming that only a single cell is selected - behaviour might not be as expected if the user selects multiple cells across >1 row

Thank you for your suggestion! I am trying the above sub and it appears to work exactly the same (in my environment the highlight occurs within any row even beyond rows 1:10. Any thoughts?
Whoa. Not sure why this made a difference, but I saved and closed out the workbook and went back in with your code and it's working now. Thank you much!
if I wanted to be able to clear all highlighting when I clicked in a particular cell (say A6) can you think of a way to recall the previous cell's formatting and exit the highlighting?
Thank you! It works perfectly and is proving already to make my work much less tedious.

Collectives™ on Stack Overflow

Adding a parameter to a worksheet event

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related