Add text in a cell based on colour of text in another cell

[Ian Richardson ACITP]

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

 

[Duncan]

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

 

[Duncan]

Actually, if running this more than once and the cells in A3 are likely
to change, then reset the words if text not red? (just add an 'else')

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"
Else
cl.Value = ""
End If
Next cl

HTH

Duncan

 

[Ian Richardson ACITP]

Hi Duncan

Thanks for the quick reply. Works like a dream!!

Ian