Coloring Cell Font based on Cell Contents

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%

Try this vba conditional formatting you can adapt it to your scenario

http://www.exceltip.com/st/Conditona...l_VBA/637.html

Simon

Bump - Anyone?


[email protected] wrote:
> 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%

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

<[email protected]> wrote in message
news:[email protected]...
> 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%
>

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

Peter T wrote:
> 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
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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%
> >