Realy Stumped..Multi Selection of Rows?
 

Man this should be easy but it's proving to be dificult.
I have a spread sheet called "Sheet1" Which looks like this minus the
notation beside the numbers in this posting. Anyways what I am trying to do
is have my code go through and select the cells which are not Highlighed
Green (colorindex 4)
However, my code does go through and select all the cells NOT highlighted
green but as soon as the next non green set of cells is found it selects them
and unselects the privous. I would like it to go through and select all of
the Non-Green Cells as though I was doing it myself manualy on the
spreadsheet while holding the control button so that previous selections are
maintained.

5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20

here's my code...

Sub Test()
Dim xRange As Range, sRange As Range, cel As Range
Set xRange = Worksheets("Sheet1").Range(Cells(1, 1), Cells(10, 3))
Set sRange = xRange.Range(Cells(1, 1), Cells(10, 1))
For Each cel In sRange
If Not sRange.Rows(cel.Row).Interior.ColorIndex = 4 Then
With xRange
.Rows(cel.Row).Select
End With
End If
Next cel
End Sub

Realy Stumped..Multi Selection of Rows?
 

Dan,

You need to build the range: I think this is what you want.

Sub Test()
Dim mySelection As Range
Dim xRange As Range, sRange As Range, cel As Range
Set xRange = Worksheets("Sheet1").Range(Cells(1, 1), Cells(10, 3))
Set sRange = xRange.Range(Cells(1, 1), Cells(10, 1))

For Each cel In sRange
With xRange
If Not sRange.Rows(cel.Row).Interior.ColorIndex = 4 Then
If mySelection Is Nothing Then
Set mySelection = .Rows(cel.Row)
Else
Set mySelection = Union(mySelection, .Rows(cel.Row))
End If
End If
End With
Next cel

mySelection.Select
End Sub

HTH,
Bernie
MS Excel MVP

"Dan Thompson" wrote in message
...
Man this should be easy but it's proving to be dificult.
I have a spread sheet called "Sheet1" Which looks like this minus the
notation beside the numbers in this posting. Anyways what I am trying to
do
is have my code go through and select the cells which are not Highlighed
Green (colorindex 4)
However, my code does go through and select all the cells NOT highlighted
green but as soon as the next non green set of cells is found it selects
them
and unselects the privous. I would like it to go through and select all of
the Non-Green Cells as though I was doing it myself manualy on the
spreadsheet while holding the control button so that previous selections
are
maintained.

5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20

here's my code...

Sub Test()
Dim xRange As Range, sRange As Range, cel As Range
Set xRange = Worksheets("Sheet1").Range(Cells(1, 1), Cells(10, 3))
Set sRange = xRange.Range(Cells(1, 1), Cells(10, 1))
For Each cel In sRange
If Not sRange.Rows(cel.Row).Interior.ColorIndex = 4 Then
With xRange
.Rows(cel.Row).Select
End With
End If
Next cel
End Sub

Realy Stumped..Multi Selection of Rows?
 

Thanks Berie That worked good :)


"Bernie Deitrick" wrote:

Dan,

You need to build the range: I think this is what you want.

Sub Test()
Dim mySelection As Range
Dim xRange As Range, sRange As Range, cel As Range
Set xRange = Worksheets("Sheet1").Range(Cells(1, 1), Cells(10, 3))
Set sRange = xRange.Range(Cells(1, 1), Cells(10, 1))

For Each cel In sRange
With xRange
If Not sRange.Rows(cel.Row).Interior.ColorIndex = 4 Then
If mySelection Is Nothing Then
Set mySelection = .Rows(cel.Row)
Else
Set mySelection = Union(mySelection, .Rows(cel.Row))
End If
End If
End With
Next cel

mySelection.Select
End Sub

HTH,
Bernie
MS Excel MVP

"Dan Thompson" wrote in message
...
Man this should be easy but it's proving to be dificult.
I have a spread sheet called "Sheet1" Which looks like this minus the
notation beside the numbers in this posting. Anyways what I am trying to
do
is have my code go through and select the cells which are not Highlighed
Green (colorindex 4)
However, my code does go through and select all the cells NOT highlighted
green but as soon as the next non green set of cells is found it selects
them
and unselects the privous. I would like it to go through and select all of
the Non-Green Cells as though I was doing it myself manualy on the
spreadsheet while holding the control button so that previous selections
are
maintained.

5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20

here's my code...

Sub Test()
Dim xRange As Range, sRange As Range, cel As Range
Set xRange = Worksheets("Sheet1").Range(Cells(1, 1), Cells(10, 3))
Set sRange = xRange.Range(Cells(1, 1), Cells(10, 1))
For Each cel In sRange
If Not sRange.Rows(cel.Row).Interior.ColorIndex = 4 Then
With xRange
.Rows(cel.Row).Select
End With
End If
Next cel
End Sub

Realy Stumped..Multi Selection of Rows?
 

Build the selection string first

Dim a As String


Range("a1").Select


If ActiveCell.Interior.ColorIndex = 4 Then a =
ActiveCell.Address
ActiveCell.Offset(rowoffset:=1).Activate
Do Until ActiveCell = ""
If ActiveCell.Interior.ColorIndex = 4 Then a =
ActiveCell.Address & "," & a
ActiveCell.Offset(rowoffset:=1).Activate
Loop
'Cells.Select a
Range(a).Select


-----Original Message-----
Man this should be easy but it's proving to be dificult.
I have a spread sheet called "Sheet1" Which looks like
this minus the
notation beside the numbers in this posting. Anyways what
I am trying to do
is have my code go through and select the cells which are
not Highlighed
Green (colorindex 4)
However, my code does go through and select all the cells
NOT highlighted
green but as soon as the next non green set of cells is
found it selects them
and unselects the privous. I would like it to go through
and select all of
the Non-Green Cells as though I was doing it myself
manualy on the
spreadsheet while holding the control button so that
previous selections are
maintained.

5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20
5 10 20 <----Cells Highlighted Green
5 10 20

here's my code...

Sub Test()
Dim xRange As Range, sRange As Range, cel As Range
Set xRange = Worksheets("Sheet1").Range(Cells(1, 1), Cells
(10, 3))
Set sRange = xRange.Range(Cells(1, 1), Cells(10, 1))
For Each cel In sRange
If Not sRange.Rows(cel.Row).Interior.ColorIndex = 4
Then
With xRange
.Rows(cel.Row).Select
End With
End If
Next cel
End Sub
.