Formula with Cell reference to include formating?

[Bob Arnett]

I would like it to display the results of a cell formula in the same format
that is in the referenced cell.
For instance (simplified):
Cell A1 is formatted in Bold and Red.
Cell A2 is formatted in Italics and Blue.
The formula in B1 is =Max(a1:a2)
Then B1 would display in Bold and Red if A1 was the higher of the two
numbers but in Italics and Blue if A2 was the higher.
Is there a way to do this?

 

[Gary''s Student]

Format > Conditional formatting... > FormulaIs > =A1=MAX(A1:A2) and pick the
format, etc.

 

[future_vba_expert]

Bob,
I got it to work using conditional formatting. Highlight B1, go to
Format, Conditional Formatting. In the drop down box, select "formula is",
then enter =(A1>A2) in the formula area. Click on the format button, then
choose the color and font that you want when A1>A2 (bold and red). Hit the
"add" button to add another condition, which will be for A2>A1, and format
for italics and blue. When both formulas are entered with the desired
formatting, hit OK. You should be good to go.

 

[Gord Dibben]

Formulas return values, not formatting.

See help on Conditional Formatting.


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

Hi,

Excel does not do that. You could write a vba macro to do it but that is
probably overkill.

You could take a snapshoot (picture) and paste it but that is probably not
what you want.

 

[future_vba_expert]

I think you would have to enter 2 formulas, with the 2 different formats, one
for A1 > A2, and one for A2 > A1. Don't see how 1 formula with MAX can do
this, but please enlighten me if it is possible. Thanks.

 

[Gary''s Student]

There are only two cases, perhaps the default format could cover one of them?

 

[future_vba_expert]

Not sure how to do that. I thought you could only do conditional formatting
under the conditional formatting tab. Where would he put the condition under
the default format for the cell? I don't see any way to do that, at least not
in my Excel 2003.

 

[Gary''s Student]

Starting with a blank worksheet, select A1 and enter 1.
Then format the cell as bold with a red font color.

Now if we apply conditional formatting value is less than 0 with a green font.

Switching between 1 and -1 will cause the font color to toggle.

 

[future_vba_expert]

I still don't see how that shows you whether a1 or a2 is the higher value, in
cell b1. That was the original problem.

 

[Gary''s Student]

Remember Bob wanted:

If the max is in A1, color me red.
If the max is in A2, color me blue.

We, arbitrarily, assume that the max is in A1 and make the default color red.
We then setup the conditional format in case we were wrong:
=A2=MAX(A1:A2) and color me blue.


This cheesy little trick can be used whenever you have only two mutually
exclusive possibilities.

 

[future_vba_expert]

I stand corrected! You are right.

I would still do it the other way, though, because you can see the full
picture under conditional formatting, with the two formulas. Otherwise you
have to look in 2 places to see what's happening.

 

[future_vba_expert]

Actually, I am using Excel 2003, and it allows 3 conditions.

 

[Gary''s Student]

I agree with you completely!!

Explicitly defining the options is MUCH better. The "cheesy" approach
should only be used if the app. is pre-2007 Excel with the old limitations on
conditional formats.