Claus Busch wrote:

Hi Terry,

Am Wed, 02 May 2018 15:02:45 +0100 schrieb Terry Pinnell:

I have entirely black text in all rows of col L and a mixture of red or
black text in column A. I've been struggling to create a formula I can
enter in column L which will leave the text unchanged if the col A cell
is red, but delete it (or substitute say 'NA') if it's black.

try:

Sub Test()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:L" & LRow).AutoFilter Field:=1, Operator:= _
xlFilterAutomaticFontColor
.Range("L2:L" & LRow).SpecialCells(xlCellTypeVisible).ClearContent s
.AutoFilterMode = False
End With
End Sub

or

Sub Test2()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A2:A" & LRow)
If rngC.Font.Color < vbRed Then rngC.Offset(, 11).ClearContents
Next
End With
End Sub


Regards
Claus B.


Excellent, thanks Claus, both work fine!

Terry, East Grinstead, UK