Dynamic Range Selection

[Todd Huttenstine]

Good afternoon!

I need to print a specific range of data but first I need
to know the cells that comprise the range. I know the
range will always start with cell D7 and will only go
through ColumnE. So now all I need is to know how far
down in rows to select. In order to do this I need for
Excel to look in ColumnD and see what the last cell is
that contains the background color light green. For
instance lets say the last cell in ColumnD that contains
the background color light green is cell D75. If this is
the case I need for it to select the range D7:E75.

What is the code that will do this?


Thank you
Todd Huttenstine

 

[JE McGimpsey]

As long as the background color is not from Conditional Formatting, one
way:

Dim i As Long
Dim nLast As Long
nLast = 7
For i = Range("D" & Rows.Count).End(xlUp).Row To 8 Step -1
If Cells(i, 4).Interior.ColorIndex = 35 Then
If i > nLast Then
nLast = i
Exit For
End If
End If
Next i
Range("D7:E" & nLast).Select

This assumes that the last light green cell will be on or above the last
filled row in column D. Adjust as necessary.

 

[Todd Huttenstine]

Thanx

-----Original Message-----
As long as the background color is not from Conditional Formatting, one
way:

Dim i As Long
Dim nLast As Long
nLast = 7
For i = Range("D" & Rows.Count).End(xlUp).Row To 8 Step -1
If Cells(i, 4).Interior.ColorIndex = 35 Then
If i > nLast Then
nLast = i
Exit For
End If
End If
Next i
Range("D7:E" & nLast).Select

This assumes that the last light green cell will be on or above the last
filled row in column D. Adjust as necessary.


.

 

[William Ryan eMVP]

You'll have to forgive my coding inelegance, been in the .NET world for a
while and I'm a bit rusty in my VBA. Anyway, this will loop through a range
and count the number of cells with the colorindex you mention. It's a bit
ugly but the logic works
Sub CheckStuff()
Worksheets("Sheet1").Select

Dim i As Integer

For Each Cell In Range("A1:A20")
Cell.Select
If ActiveCell.Interior.ColorIndex = 35 Then
i = i + 1
End If
Next
Range("A21").Select
ActiveCell.Value = i

End Sub

 

[Tom Ogilvy]

Assumes cells are not empty in Column D for as far down as you want to
check.

Dim lastRow as Long
Dim rng as Range
lastRow = 0
set rng = Range("D7")
Do while not isempty(rng)
if rng.interior.colorIndex = 35 then
lastRow = rng.row
end if
set rng = rng.offset(1,0)
Loop
Set rng = Range(Range("D7"),Cells(lastRow,4)).Resize(,2)
rng.select

Can't guarantee that 35 is the light green you speak of.

Select one of the cells and run this

Sub ShowColorIndex
msgbox ActiveCell.Interior.ColorIndex
End Sub

If that number doesn't show 35, then change my code to match that number.