EXCEL 2003*FORMULA TO HILIGHT SPECIFIC CELLS WITH TRUE RESULTS?

[tlc]

Hi,
Does anyone know if there is a formula I can add into a formula already
written that will highlight the cells that return true results and the
primary formula cell too?

When the below formula, located in cell B24, returns the true value, I want
it to highlight itself and each of the referenced cells causing the true
value.

=IF(OR(B19>0.5,B20>0.5,B21>0.5,B23>0.5),"QUOTE IN MD OVER .500""",0)
Any help would be wonderful.

 

[JBeaucaire]

Highlight cells B19:B24, click on FORMAT > CONDITIONAL FORMATTING

Condition1 - FormulaIs:

=$C$15="QUOTE IN MD OVER .500"""

....and then click FORMAT and setup the colors you want. Now they will all
change color at the same time.

Will that work for you?

 

[JBeaucaire]

My apologies...that formula should be:

=$B$24="QUOTE IN MD OVER .500"""

Is that better?

 

[tlc]

Thank you so much for responding JB,

I seem to be suffering an 'operator error' moment, I entered the information
as you were kind enough to provide, but it isn't changing the format for some
reason. I understand how it is supposed to work though (thank you for that
too). Can you think of any reason it wouldn't respond correctly, like the
cells are locked or hidden, or...?

 

[David Biddulph]

Firstly, go back into CF and check that you've got exactly what it says
(checking the obvious things like making sure that you've used Formula Is,
not Cell Value Is, and that you haven't got additional quote marks beyond
what was suggested). I hope that you copied the formula from here and
pasted it into the Formula Is dialogue in CF, and that you didn't try to
retype it. If in doubt, copy the content of the "Formula Is" dialogue box
and paste it back in here. Check also that your B24 formula is exactly as
you posted in your previous message. Again, if in doubt copy B24's formula
bar and paste in here so that it can be checked.

Secondly, use a spare cell and put the formula =$B$24="QUOTE IN MD OVER
..500""" into the formula bar of the cell so that you can see whether it
returns TRUE or FALSE.

If all else fails and you still can't understand why it's not working, you
could change the condition from
=$B$24="QUOTE IN MD OVER .500"""
to
=OR(B$19>0.5,B$20>0.5,B$21>0.5,B$23>0.5)

This ought to give exactly the same result, but is an alternative to try if
you are totally stuck.

 

[tlc]

Dear David and JB,

Great help from you both. I finally overcame my 'operator error'. I
realized the cell B24 was merged with A24 and C24 making the correct entry
=$A$24=....
I also discovered that both versions formatted every one of the cells B19 -
B23 if the condition appeared in just one. Thanks to your help and insight,
I figured out I needed to conditional format each cell individually to change
only the cell with the over .5 entry and cf cell A24 "Quote in...." to even
better results than I had hoped
Now it works to perfection!

Thank you both so very much.
Your help is so very much appreciated.

 

[JBeaucaire]

I *hope* you didn't manually type in all those CF cells. You don't have to do
that.

You just need to highlight all the cells that will use this CF, open the CF
window and enter the formula but remove the $ flags, so $B$24 (absolute
reference) becomes just B24 (relative reference). Now Excel will adjust the
reference for each of the cells highlighted FOR you.