Dim CRng As Range
Set CRng = Range("J3:J1311")
For Each cl In CRng.Cells
If cl.Offset(0, -9).Font.ColorIndex = 3 Then
cl.Value = "For Sale"
End If
Next cl

Hope this Helps

Duncan


Ian Richardson ACITP wrote:

Hi

I have recorded a macro to add "For Sale" into cell J3 if the text in column
A3 is red. I added an IF THEN and it works but how do I get it to repeat in
all cells as in cell J4 needs to be set to "For Sale" if the text in cell A4
is red all the way down to J1311.

Sub Macro4()

Range("A3").Select
If Selection.Font.ColorIndex = 3 Then
Range("J3").Select
ActiveCell.FormulaR1C1 = "For Sale"

End If
End Sub

Thanks in advance

Ian