David,

I just came across this thread and had an additional question..I see how to
conditionally format cells that have formulas, but is there a way to put a
different format on cells that are not only formulas, but have external
workbook references..

For example, if it is an internal workbook referenced formula, i would just
change the color of the numbers to BLUE, but if the formula contained and
external reference to another workbook, i would want the cell to be colored
ORANGE, if the number is a hard code, make the cell GREEN..

Any suggestions without writing VBA macros? or if a macro is the only
way..can you provide as i don't know VBA at all.

thanks in advance for your assistance.
--
Mike


"David McRitchie" wrote:

Biff has provided the correct answer for Claes (in my opinion)
but the implementation requires a more complex formula for
the Conditional Formatting than is provided in John's tip 45.

First set up a Defined Name as shown in John Walkenbach's
http://j-walk.com/ss/excel/usertips/tip045.htm
which has to be set up in any workbook it is to be used in.
The advantage of this method is that only Worksheet
Functions are used so it calculates faster than if programming
were used.

The OP (Original Poster) knows which columns normally have
formulas and those are the columns that would be conditionally
formatted (colored). So if Column E is the only column that
would start out with all formulas and the some of the formulas
would be overwritten manually with constants, you would
- Select Column E and if you are at the top then cell E1
would be the active cell. It is the active cell address that
must be used in the formula.
- Format, Conditional Format,
Condition 1, Formula is:
=AND(NOT(CellHasFormula),NOT(ISBLANK(E1)),NOT(ROW( E1)=1))

- Format button, Patterns (tab), choose a light pastel color

John Walkenbach's web page example colors the cells if it has a
formula. Claes wants the opposite, and of course one would not
want to mark the first row with column titles, nor the empty cells
on sheet, beyond the used range. The formula I provided would
not be identifying cells that have been manually wiped out (cleared
out) within the used range -- I hope that would not be a problem.

If there were more than one column this was to be applied to you
would select those columns and write your formula based on the
active cell. For instance my testing was done by selecting ALL
cells (Ctrl+A, except in Excel 2003) and cell A1 was the active cell.

For a better understanding of Conditional Formatting you might also
look at: http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Biff" wrote in message ...
Hi!

Take a look at this:

http://j-walk.com/ss/excel/usertips/tip045.htm

Biff

-----Original Message-----
I run this question again.
I am novice in VBA so I have no idea how to make User
Defined Formulas
as written i earlier discussions.

I have cells with formulas picking up data from a linked
file.
On and off I overwrite the cell formula with figures
(1234).
Then I want the conditional formatting to change the cell
color
to remind me that I have overwritten the formula.

I suppose the answer still might be VBA, but can you then
explain to a
novice/rookie how to do it?

Regards,
Claes
.