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