I have a spreadsheet that calculates the value of a stock portfolio
based on position, FX rates and the current price. The spreadsheet is
saved every night with the latest positions and last recorded prices,
and use a formula to calculate the value.

I also link this spreadsheet to Bloomberg using their AddIn. Since not
all stocks are priced on BBG, I place the AddIn formula in another
hidden column, and copy the price from that column into the "last
recorded price" column only if ISERROR is false. That way if BBG has a
price the price column is updated, and if not, the last price is not
removed and you still get a useful number.

What I would like to do is give the user some visual indication of
which price they are using. I am currently coloring the price cells
blue if the stock _can_ be priced on BBG (ie, I have a ticker).
However, that doesn't mean it _is_ priced on BBG, so the cell is
turning blue even in cases where the price is not live.

What I can't figure out is how to change the color based on a formula.
I am aware of Conditional Formatting, but the spreadsheet is large and
I recall performance being fairly poor. Can anyone offer some advice
here?

Maury

Why not add conditional formatting to a copy of your file so that YOU can
judge if there is a performance issue?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Maury Markowitz" <(E-Mail Removed)> wrote in message
news:61e0b200-7d83-47af-8235-(E-Mail Removed)...
>I have a spreadsheet that calculates the value of a stock portfolio
> based on position, FX rates and the current price. The spreadsheet is
> saved every night with the latest positions and last recorded prices,
> and use a formula to calculate the value.
>
> I also link this spreadsheet to Bloomberg using their AddIn. Since not
> all stocks are priced on BBG, I place the AddIn formula in another
> hidden column, and copy the price from that column into the "last
> recorded price" column only if ISERROR is false. That way if BBG has a
> price the price column is updated, and if not, the last price is not
> removed and you still get a useful number.
>
> What I would like to do is give the user some visual indication of
> which price they are using. I am currently coloring the price cells
> blue if the stock _can_ be priced on BBG (ie, I have a ticker).
> However, that doesn't mean it _is_ priced on BBG, so the cell is
> turning blue even in cases where the price is not live.
>
> What I can't figure out is how to change the color based on a formula.
> I am aware of Conditional Formatting, but the spreadsheet is large and
> I recall performance being fairly poor. Can anyone offer some advice
> here?
>
> Maury