|
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% |
Coloring Cell Font based on Cell Contents
|
Coloring Cell Font based on Cell Contents
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 wrote in message oups.com... 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% |
Coloring Cell Font based on Cell Contents
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 wrote in message oups.com... 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% |
| All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com