Hi
Do the formatting in one go:
Set myRange = Nothing
For Each c In r
If c.Value = "Complete" Or c.Value = "Not Applicable" Then
Set myRange = Union(myRange, Cells(c.Row, c.Column - 28) )
end if
Next c
With myRange
.ClearFormats
.Interior.ColorIndex = 5
.Font.ColorIndex = 2
.Font.Name = "Verdana"
.Font.Size = 8
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.NumberFormat = "dd/mm/yy"
End With

regards
Paul
On May 8, 11:41*am, Ladymuck
wrote:
I have the following that is part of a larger piece of code that formats a
worksheet. Currently, this part takes 15 minutes to run and I would like to
know if I could have written this code in a better way:

* * For Each c In r
* * * * If c.Value = "Complete" Or c.Value = "Not Applicable" Then
* * * * * * With Cells(c.Row, c.Column - 28)
* * * * * * * * .ClearFormats
* * * * * * * * .Interior.ColorIndex = 5
* * * * * * * * .Font.ColorIndex = 2
* * * * * * * * .Font.Name = "Verdana"
* * * * * * * * .Font.Size = 8
* * * * * * * * .HorizontalAlignment = xlCenter
* * * * * * * * .VerticalAlignment = xlTop
* * * * * * * * .NumberFormat = "dd/mm/yy"
* * * * * * End With
* * * * End If
* * Next

This is running on a area that is 203 rows x 17 columns, the number of rows
varies.

I cannot use conditional formatting as it already exists in the target cells
and has to be removed and reformatted as above. Unless there is a better way,
of course!

Many thanks for any suggestions
Louise