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?
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?