Highlight non- contiguous rows
 

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

Highlight non- contiguous rows
 

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)

"Steve" wrote in message
...
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

Highlight non- contiguous rows
 

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


-----Original Message-----
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


.

Highlight non- contiguous rows
 

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


"Brad" wrote in message
...
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


-----Original Message-----
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


.

Highlight non- contiguous rows
 

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)

"Steve" wrote in message
...
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


"Brad" wrote in message
...
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


-----Original Message-----
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


.

Highlight non- contiguous rows
 

Thanks again, Bob...

--Steve


"Bob Phillips" wrote in message
...
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)

"Steve" wrote in message
...
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


"Brad" wrote in message
...
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


-----Original Message-----
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


.