View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default 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