Conditional Formatting to differentiate between Input and Calcs

[Ditch]

Hi Team,
I want to set up a spreadsheet such that data input displays a black font
but any formula or result from another tab has blue font.
That way users automatically differentiate between input cells & calculated
cells. I've also thought about grey backgrounds for calculated cells- maybe
a bit too heavy?
Any Finance best practice tips or examples would be welcome
Thanks,
Ditch

 

[Dave Peterson]

I wouldn't use format|conditional formatting.

I'd take a simpler approach.

Select a range that includes the formulas that you want to format--you can
include other stuff, too.

Edit|goto (or ctrl-g or F5)|Special|Formulas
and click ok

Your original selection has been reduced to just the cells with the formulas.
Change the formatting the way you want.

You could do the same thing with input cells, but I bet there are headers,
descriptions, notes, ... that shouldn't change format.

I'd just select those cells manually (click on the first and ctrl-click on
subsequent) and format them manually. (I'd also do it a few at a time--I hate
when I select the wrong cell and have to start my selection over from scratch!)

 

[Ditch]

Thanks Dave,
I was trying to set this up from the point of view of my staff such that
when they typed in a formula starting with '=' it would autmatically change
the font such that we could all be disciplined in differentiating between
input & calculated cells.
I take your point about setting ranges and formatting cells collectively.
Thanks,
Ditch

 

[Dave Peterson]

You could use Format|Conditional formatting--but it won't take effect until
after the user finishes typing the entry.

And I'd use a UDF (user defined function) that returned true or false --
depending on whether that cell has a formula in it.

This goes in a General module in the workbook's project:

Option Explicit
Function IsFormula(Rng As Range) As Boolean
IsFormula = Rng.Cells(1).HasFormula
End Function

Then with A99 the activecell, you could use a conditional formatting rule:

Formula is:
=isformula(a99)

Give it a nice format.