I'm a nooby. VB code to filldown for varying spreadsheet length

[gviteri]

Hi everyone. I'm new to the vb world and have been splicing macros together for a bit, but I can't figure out the filldown portion.

My goal: Create a macro that changes the format of cells in column H determined by the contents of corresponding cells in column C. Then, filldown the proper amount of cells. I think my code has to be changed to a For, or While looping statement?

Essentially, I want it to loop while there are still values in both fields.

Here's what I have:

Sub format()
Range("H3").Select
If Range("C3").Value = "1" Then
Selection.NumberFormat = "$#,##0.00"

Else
If Range("C3").Value = "5" Then
Selection.NumberFormat = "0.00%"

End If
End If
End Sub



My problem is that I don't know how to fill down for a varying length, or how to approach it. Can anyone help?

 

[gviteri]

ho ho!!! I got it!


Sub format()
Dim x As Long
Dim y

x = ActiveSheet.Range("H3", ActiveSheet.Range("H3").End(xlDown)).Rows.Count
y = 3 ' starts at cell 3

Do
Range("H" & y).Select
If Range("C3").Value = "1" Then
Selection.NumberFormat = "$#,##0.00"
y = y + 1
x = x - 1
Else
If Range("C3").Value = "5" Then
Selection.NumberFormat = "0.00%"
y = y + 1
x = x - 1
End If
End If
Loop While x > 0
End Sub