Solved: Specifying last row range

[Klartigue]

I have this code to highlight cells with value <100k.

[VBA]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
[/VBA]

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]

Is this what you mean?


[VBA]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[/VBA]

[Klartigue]

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]

[VBA]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
[/VBA]

[Klartigue]

That works great, thank you!!!

[Klartigue]

How does the resize(3,10) work? I am trying to write a similar code and dont understand what that specifies?

Thanks

[Klartigue]

I figured it out, I was just referencing the wrong thing:

[VBA]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
[/VBA]