Try

Dim R As Range
Set R = Range("F4")
Do Until R.EntireRow.Cells(1, "A").Value = vbNullString
With R
.FormulaR1C1 = "your formula here"
With .Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlColorIndexAutomatic
End With
.Locked = True
End With
Set R = R(2, 1)
Loop


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]


On Mon, 25 Jan 2010 15:08:01 -0800, Lucas B
wrote:

I am trying to write a macro that will do the following as long as there is
data in column A.

Range("F4").Select
ActiveCell.FormulaR1C1 _
= "=IF(LEN(R4C1)<LEN(R4C5),
CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))"
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Selection.Locked = True
End With

How do I write a Loop that will keep doing this on each successive row until
the cell in column A is blank?