Hi,
I has a worksheet which gets data for a week range. My coloumns are fixed i.e one week range. But Rows can be differed and i need to calculate avg and based on avg i need to color the rows.
My format is like this :
A B C D E F G H
1
2
3
4
5
X
H is the coloumn where i take the avg. I have written VBA code by hard coding the row value, but in general it will vary. I am not sure how to find the last row and then calculate Avg.
I am adding the code which i did for constants Rows.
![]()
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])" Range("H2").Select Selection.AutoFill Destination:=Range("H2:H90") Range("H2:H90").Select Columns("H:H").Select Selection.NumberFormat = "0" Dim Rng As Range, cell As Range Set Rng = Range("A2:G90") Rng.Interior.ColorIndex = xlNone For Each cell In Rng If cell.Value < Range("H" & cell.Row) * 0.2 Then cell.Interior.ColorIndex = 3 ElseIf cell.Value > Range("H" & cell.Row) * 0.2 And cell.Value < Range("H" & cell.Row) * 0.5 Then cell.Interior.ColorIndex = 6 End If Next cell
Could you please help me out in writing VBA code for getting last row and do the calculation instead of hardcoding the row range.
Thanks & Regards,
Pradeep
Bookmarks