Realy Stumped..Multi Selection of Rows?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Thanks Berie That worked good :)


Bernie Deitrick said:
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
Click to expand...
 
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
 
Back
Top