reverse conditional formatting

[jwbuyer]

I want a formula to return the the text string "new" if another cell in the
same row has red colored text. How do I do this?

 

[Mike H]

Hi,

If you post the conditional format formula that turns the text red then it
should be possible write a formula that will check the same condition and
return the text string you requires but what you can't do is have a formula
along the lines of

=if(a1=red text,"new",something else)

Mike

 

[jwbuyer]

the problem is that I was handed a worksheet that has the cells formatted
manually as opposed to being conditionally formatted.

 

[Mike H]

Hi,

Alt +F11 to open vb editor right click 'this workbook' and insert module
and paste the code below in.

call with

=isred(A1)

Function isred(rng As Range) As String
Application.Volatile
If rng.Font.ColorIndex = 3 Then
isred = "New"
Else
isred = ""
End If
End Function

Mike

 

[jwbuyer]

did I do something wrong, I keep getting the error that "this formula takes
no arguments." Forgive me, this is my first time editing visual basic in
excel

 

[Dave Curtis]

Hi,
If the red font is NOT a result of conditional formatting, then by
downloading the "morefunc" you can use on of the formulas from that.
Assuming your text is in A1, then in A2, enter
=IF(XLM.GET.CELL(24,A1)=3,"new","")
and copy dow as far as necessary.

The 24 tells the function to look for the font colour, and the 3 is the
colour index for red.

The function doesn't update automatically, so you need to force a recalc,
for instance by pressing F9.

Dave

 

[jwbuyer]

The morefunc route worked like a charm, and was exactly what I was looking
for. Thanks much