PDA

View Full Version : Solved: Specifying last row range



Klartigue
08-13-2012, 07:47 AM
I have this code to highlight cells with value <100k.

Sub Lessthan100k()

Dim cll As Range, rng As Range
Set rng = Range("I53:R" & Cells(Rows.Count, "I").End(xlUp).Row)
For Each cll In rng

If cll.Value < 100000# Then cll.Select

With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With

Next cll
End Sub


But my sheets are always going to be a different number of rows so instead of setting the range as I53:R, i need to me more general and set the range as:

lastrow = .Cells(.Rows.Count, "I").End(xlUp).Row + 2
.Cells(lastrow + 1, 9) to .Cells(lastrow + 1, 18)

Any ideas on how to do this correctly to get a more general code to specify my range?

Bob Phillips
08-13-2012, 08:00 AM
Is this what you mean?


Sub Lessthan100k()

Dim cll As Range, rng As Range
With ActiveSheet

Set rng = .Range("I53:R" & .Cells(.Rows.Count, "I").End(xlUp).Row + 3)
For Each cll In rng

If cll.Value < 100000# Then

With cll.Font

.Color = -16776961
.TintAndShade = 0
End With
End If
Next cll
End With
End Sub

Klartigue
08-13-2012, 08:00 AM
On the above post, here is the sheet I am working with. I am trying to define the range highlighted in green in terms of lastrow.

Bob Phillips
08-13-2012, 08:05 AM
Sub Lessthan100k()

Dim cll As Range, rng As Range
With ActiveSheet

Set rng = .Range("I" & .Cells(.Rows.Count, "A").End(xlUp).Row + 3).Resize(3, 10)
For Each cll In rng

If cll.Value < 100000# Then

With cll.Font

.Color = -16776961
.TintAndShade = 0
End With
End If
Next cll
End With
End Sub

Klartigue
08-13-2012, 08:18 AM
That works great, thank you!!!

Klartigue
08-13-2012, 08:24 AM
How does the resize(3,10) work? I am trying to write a similar code and dont understand what that specifies?

Thanks

Klartigue
08-13-2012, 09:09 AM
I figured it out, I was just referencing the wrong thing:

Sub Macro5()
'
Dim cll As Range, rng As Range
With ActiveSheet

Set rng = .Range("H" & .Cells(.Rows.Count, "A").End(xlUp).Row + 3).Resize(3, 11)
For Each cll In rng

With cll.Interior

.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Next cll
End With

End Sub