Coloring Cell Font based on Cell Contents

[willjohnson33]

I create alot of financial models at work and I typically use the
following font color codes so that I can better audit the model later.

Row & Column Headings Always Black

Blue: Hard Number Input
Black: Calculation
Green: Hard Number Input used as a driver of the model
Purple: Link to another sheet or workbook
Red: Formula and a hard input number in it. Example (=A1 + 5)

I am trying to figure out a way to create a function that either always
updates based on they type of input in a cell or that I can just use a
shortcut key that goes throught the sheet to update all the cells. I
currently define a name called FormulaInCell
=GET.CELL(48,INDIRECT("rc",FALSE))and then just select all cells and
use conditional formatting to make that black and it colors all
formulas black. However I would like it to be more robust so that it
covers the situations mentioned above. There must be a way to do it,
but I just can't seem to figure one out. Any help is greatly
appreciated.

Will

As an example:
2001 2002 2003 2004E 2005E
Sales 100.0 110.0 120.0 129.0 137.4
Sales Growth 7.0% 10.0% 9.1% 7.5% 6.5%
Profit 52.0 53.0 58.0 60.6 63.9
Profit Margin 52.0% 48.2% 48.3% 47.0% 46.5%

In this model:
2001-2003 Sales are Blue because they are hard coded inputs
2001-2003 Growth is Black because it is a formula
2001-2003 Profit is Purple because it is linked from another sheet
2001-2003 Profit Margin is Black because it is a formula

2004E-2005E sales are black and calculated as
previous years sales * (1+ sales growth)
2004E-2005E Growth is hardcoded, but green because it drives the sales
number
2004E-2005E Profit is Black and calculated as Sales * Profit Margin
2004E-2005E Profit Margin is Red Because it is calculated as the
previous years profit margin + .1%

 

[willjohnson33]

Bump - Anyone?

 

[Peter T]

I sort of assume you don't have a template but build each new sheet roughly
along similar lines. What I don't understand is why, having done that,
anything changes except new inputs but only into pre determined cells.

If it's simply a matter of formatting a newly constructed sheet, maybe Name
all your sections, then in code something like

Range("HardNumberInput").Font.ColorIndex = 5

If you have similar sheets in the same workbook, probably better to use
Worksheet-Level names. This way each sheet can use same names referring to
ranges on their own sheets. Define like this:
Sheetname!HardNumberInput (note the ! Also may need to embrace sheetname
with apostrophes if sheetname includes certain characters)

Providing the sheet is active when you run your code, you don't need to
qualify with the defined name with sheetname.

To find and selecting formulas, record a macro, press F5, specialcells,
formulas.
Links to other sheets a bit harder. But if you know where these are already
no problem if you Define as suggested above.

Regards,
Peter T

 

[willjohnson33]

The example I gave was extremely simple and most models are built from
scratch with different inputs. I guess it is just something that is
much more difficult than I had expected.

Thanks