Problems looping through the sheet

[dfwconsumables]

I have a worksheet, there is currently 14 columns with information in them, only 6 of the columns are important. There are currently 3,500 rows in the workbook. I have created a macro that loops through column E and highlights the range C:N for any value greater than 1. That value is in a summed row in Column E (like a pivot table). Call that summed amount the variable i, I would like to offset the row up, so a negative i and then highlight columns C:N. I have tried 10 differing ways, but I just can't get my code code to work. My code snippet is below, please help with any comments, I'm new to this so please be gentle.

Sub VerificationLine()
Dim i As Long, r1 As Range, r2 As Range
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row


For i = 3 To FinalRow
Set r1 = Range("e" & i)
Set r2 = Range("C" & i & ":N" & i)
If r1.Value = 1 Then r2.Interior.Color = xlNone
If r1.Value > 1 Then r2.Interior.Color = vbYellow
Next i
End Sub

 

[Norie]

So if E5 was greater than 1 you would want to highlight C4:N4?

If that's right try this.

Sub VerificationLine()
Dim i As Long, r1 As Range, r2 As Range

    FinalRow = Cells(Rows.Count, 3).End(xlUp).Row

    For i = 3 To FinalRow
        Set r1 = Range("e" & i)
        Set r2 = Range("C" & i - 1 & ":N" & i - 1)
        If r1.Value = 1 Then r2.Interior.Color = xlNone
        If r1.Value > 1 Then r2.Interior.Color = vbYellow
    Next i
    
End Sub

 

[mumps]

Sorry. I didn't see Norie's response.

 

[dfwconsumables]

That is almost correct. I want C through N10 to highlighted but more importantly, I want the total in E10, to offset the rows to highlight so that row 9, 8, and 7 are highlighted as well

 

[Norie]

Do you mean the value in E10 determines the no of rows above to highlight?

 

[dfwconsumables]

That is exactly correct. I'm sorry my description isn't all that great

 

[dfwconsumables]

Do you mean the value in E10 determines the no of rows above to highlight?

That is right, the value in E10 (which is the first item with a value greater than one) would be the amount of rows to move through, and that is move up through.