Highlight non- contiguous rows

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I am trying to create code which will highlight non-contiguous rows in a
spreadsheet, using a worksheet change event procedure. Every time the target
column value changes (column "E") I would like select that row, but without
de-selecting the previous entry.

Example:
change value of cell "E1" to "3", event triggers row 1 to select.
change value of cell "E4" to "7", event triggers row 4 to select, but row 1
stays selected also.

Is this possible, or would I have to highlight the rows, instead of
selecting.


Thanks,

Steve
 
Steve,

How about this?

Dim oRng As Range

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 5 Then
If oRng Is Nothing Then
Set oRng = Target.EntireRow
Else
Set oRng = Union(oRng, Target.EntireRow)
End If
oRng.Select
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Something like this maybe?


Dim rngSelect As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub ' Make sure it's
column E.
If rngSelect Is Nothing Then ' Create the range if it
doesn't exist.
Set rngSelect = Target.EntireRow
Else ' If it does exist, add
it to the range
Set rngSelect = Union(rngSelect, Target.EntireRow)
End If
rngSelect.Select ' Select the range.
End Sub


Hope that helps.

-Brad
 
Thanks, guys...

Both work great, but how do I (via a command button) reset the selection
when I need to?
(so no rows are selected...)


--Steve
 
In mine

Private Sub Commandbutton1_Cl;ick()

Set rngSelect = Nothing
Range("A1").Select

End Sub
Make sure it is in the same worksheet code module

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks again, Bob...

--Steve


Bob Phillips said:
In mine

Private Sub Commandbutton1_Cl;ick()

Set rngSelect = Nothing
Range("A1").Select

End Sub
Make sure it is in the same worksheet code module

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top