Formula that tests text color

[Alan Armstrong]

I need a cell formula that does this:

IF (foreground text color=whatever, action 1, action 2)

Is there a simple way that doesn't require screeds of code?

Alternatively, is there some other visible cell property I could change
(such as bold) to switch between actions?

Alan

[Bob Phillips]

Function TextColour(rng As Range)
If rng.Cells.Count > 1 Then
TextColour = CVErr(xlErrRef)
Else
TextColour = rng.Font.ColorIndex
End If
End Function

IF(TextColour(A1),1,2)

but it won't refire if the cell changes colour, as that does not trigger a
sheet recalculation.

--
HTH

Bob Phillips

"Alan Armstrong" <mirihika@clear.net.nz> wrote in message
news:eIby$LoZFHA.3364@TK2MSFTNGP12.phx.gbl...
> I need a cell formula that does this:
>
> IF (foreground text color=whatever, action 1, action 2)
>
> Is there a simple way that doesn't require screeds of code?
>
> Alternatively, is there some other visible cell property I could change
> (such as bold) to switch between actions?
>
> Alan
>
>

[Alan Armstrong]

Thank you, Bob. I need a bit more help. please.

I saved your code as Module1 in VBA then tried using the formula in a cell.
But there are some problems:

1. None of the color coding systems I could find in Excel Help works.

2. The formula persistently capitalises the U like this and I can't change
it:

=IF(TextColoUr(E5)=0,1,2)

and try as I might it always returned 2.

3. I closed and re-opened Excel. That gave me a 'disabled' security warning
and I tried setting the level to 'low' but that made no difference.

What am I doing wrong, and where do I find workable colour codes, please?
The only colour I actually need is MS Brown RGB(153,51,0)

Alan

[Bob Phillips]

Alan,

You can crack the capital U problem by changing the name :-)

Function TextColor(rng As Range)
If rng.Cells.Count > 1 Then
TextColor = CVErr(xlErrRef)
Else
TextColor = rng.Font.ColorIndex
End If
End Function

As to testing the value, I don't think there is a color 0. You could always
set a cell to that colour to test against, say A1, and then use

=IF(TextColor(E5)=TextColor(A1),1,2)

--
HTH

Bob Phillips

"Alan Armstrong" <mirihika@clear.net.nz> wrote in message
news:%23TI6KBxZFHA.596@TK2MSFTNGP10.phx.gbl...
> Thank you, Bob. I need a bit more help. please.
>
> I saved your code as Module1 in VBA then tried using the formula in a
cell.
> But there are some problems:
>
> 1. None of the color coding systems I could find in Excel Help works.
>
> 2. The formula persistently capitalises the U like this and I can't change
> it:
>
> =IF(TextColoUr(E5)=0,1,2)
>
> and try as I might it always returned 2.
>
> 3. I closed and re-opened Excel. That gave me a 'disabled' security
warning
> and I tried setting the level to 'low' but that made no difference.
>
> What am I doing wrong, and where do I find workable colour codes, please?
> The only colour I actually need is MS Brown RGB(153,51,0)
>
> Alan
>
>

[Alan Armstrong]

Thanks for a neat bit of lateral thinking, Bob.

Almost there but I still have a problem - it won't update when I change font
colour! F9 doesn't update it either. However changing the 'value if false'
in the formula does force an update.

I've juggled security settings, digitally signed the module (forged
signature) and set Enable Macros on opening without effect.

Wondering if it has anything to do with putting the VBA code in a module of
a password-protected worksheet? Is there somewhere else I should have put
it?

I probably should have told you earlier I am using Excel 2002.

Alan

[Bob Phillips]

Alan,

No, you have found the intrinsic problem of this code. The changing of
colour, text or cell, does not trigger a sheet recalculation. You could make
it volatile, then any change that causes a recalc on the sheet will trigger
this function, or you can force it, F9

Function TextColor(rng As Range)
Application.Volatile
If rng.Cells.Count > 1 Then
TextColor = CVErr(xlErrRef)
Else
TextColor = rng.Font.ColorIndex
End If
End Function

But it will still not fire on the event of a colour change. Now way that I
know around that I am afraid

--
HTH

Bob Phillips

"Alan Armstrong" <mirihika@clear.net.nz> wrote in message
news:uDOq$m9ZFHA.2664@TK2MSFTNGP15.phx.gbl...
> Thanks for a neat bit of lateral thinking, Bob.
>
> Almost there but I still have a problem - it won't update when I change
font
> colour! F9 doesn't update it either. However changing the 'value if false'
> in the formula does force an update.
>
> I've juggled security settings, digitally signed the module (forged
> signature) and set Enable Macros on opening without effect.
>
> Wondering if it has anything to do with putting the VBA code in a module
of
> a password-protected worksheet? Is there somewhere else I should have put
> it?
>
> I probably should have told you earlier I am using Excel 2002.
>
> Alan
>
>

[Alan Armstrong]

That is working well enough for me, thank you Bob! I am old enough to have
grown up with manual change gear shifts! An automatic is still the icing on
the cake.

Your time and patience are appreciated, and I have learnt a few other things
in the process. Thank you again.

Alan