Function based on colour of cell text?
 

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.

But I'm coming to the conclusion this is impossible, as there appear to
be no functions of the form 'IF cell text colour = red'.

Before I resort to doing it with a Macro Express Pro macro (testing
pixel colours at a few places), is there another method please,
including perhaps a VBA macro?

Terry, East Grinstead, UK
Using Excel 365 on a Win 10 Pro PC

Function based on colour of cell text?
 

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.
--
Windows10
Office 2016

Function based on colour of cell text?
 

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