If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting to differentiate between Input and Calcs
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 |
#2
|
|||
|
|||
Conditional Formatting to differentiate between Input and Calcs
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 wrote: 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 |
#3
|
|||
|
|||
Conditional Formatting to differentiate between Input and Calc
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" wrote: 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 wrote: 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 |
#4
|
|||
|
|||
Conditional Formatting to differentiate between Input and Calc
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. Ditch wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|