Excel VBA Looping formula to change Range

Question

I've written a code to manipulate data in L9 : DC9, But now I need to repeat this for L10 : DC10, L11 : DC11 and etc.. I've tried a For Next loop replacing the value in the range with Li:DCi and specifying (i) as 9 to 30 but I get an error. How can I make a loop for this function?

My current version of Excel is 2013

Answer 1

What you are looking for is a syntax like this

Sub LoopRows()
    Dim i As Integer
    For i = 9 To 30
        ActiveSheet.Range("L" & i & ":DC" & i).Interior.Color = RGB(100, 100, 100)
    Next i
End Sub

This example just formats the color of the cell in each row. Notice how I use the for-loop to create a looping range selection.

Answer 2

I suggest using Range("L9").Resize(21,50).Interior.Color = .. to do it in one statement.