Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 2nd 18, 03:02 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 142
Default 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   Report Post  
Old May 2nd 18, 03:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,604
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
  #3   Report Post  
Old May 3rd 18, 09:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 142
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i change cell text colour based on value in other cell Oscarrot Excel Worksheet Functions 2 July 20th 09 03:21 PM
Insert text based on another cell's colour or font in Excel 2003 Philip Hinton Excel Discussion (Misc queries) 3 December 5th 07 12:48 AM
Insert text based on another cell's colour or font in Excel 2003 Philip Hinton Excel Worksheet Functions 3 December 5th 07 12:48 AM
Cell Colour based on text in Range of cells viewmaster[_2_] Excel Programming 2 March 3rd 06 03:53 AM
Cell Colour based on text in Range of cells viewmaster Excel Programming 1 March 3rd 06 03:42 AM


All times are GMT +1. The time now is 02:24 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017