Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula that tests text color
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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" wrote in message ... 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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenated text to formula | Excel Discussion (Misc queries) | |||
Referencing a formula (as text) | Excel Worksheet Functions | |||
Coverting Formula to Text | Excel Discussion (Misc queries) | |||
Formula to Text to Formula | Excel Discussion (Misc queries) | |||
Formula Color Coding | Excel Discussion (Misc queries) |