Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i change cell text colour based on value in other cell | Excel Worksheet Functions | |||
Insert text based on another cell's colour or font in Excel 2003 | Excel Discussion (Misc queries) | |||
Insert text based on another cell's colour or font in Excel 2003 | Excel Worksheet Functions | |||
Cell Colour based on text in Range of cells | Excel Programming | |||
Cell Colour based on text in Range of cells | Excel Programming |