Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi Selection List/Combi Box | Excel Discussion (Misc queries) | |||
Preventing Multi Cell Selection | Excel Discussion (Misc queries) | |||
Listbox multi selection | Excel Programming | |||
Best way to add Multi selection box | Excel Programming | |||
multi tab selection prohibition | Excel Programming |