Stumped: If a cell contains a formula, can you make text color automatically change?

Q

qwopzxnm

Hello all-

I tried searching and couldn't find this.

I was wondering if there's a way to change the text color in a cel
that contains a formula.

My goal is to build a financial model with cells that have blue tex
for cells with formulas, and regular black text for hard coded numbers
This will allow the users to easily identify which numbers can b
adjusted, and which are coming from a formula.

If there is an easier, or better way to do this I'm all ears (or eye
for the forums
 
P

Pete_UK

You can use conditional formatting for this. You might also consider
unlocking the cells which have "adjustable" numbers in and then
protecting the worksheet, so that your formulae do not accidentally get
over-written. I usually use a bright yellow background as well to
indicate to users where they can enter or change data (i.e. the
unlocked cells), and so the normal white background indicates that they
cannot change anything in that area.

Hope this helps.

Pete
 
M

Max

qwopzxnm said:
I was wondering if there's a way to change the text color in a cell
that contains a formula.
My goal is to build a financial model with cells that have blue text
for cells with formulas, and regular black text for hard coded numbers.
This will allow the users to easily identify which numbers can be
adjusted, and which are coming from a formula.
Click to expand...

Press F5 > Special > Check "Formulas" > OK
will select all formula cells on the sheet at one go
Then we could apply Format > Cells > Font tab (to taste)

Similarly, we could also select the constants:
Press F5 > Special > Check "Constants" > OK
then format ...
 
Q

qwopzxnm

Max and Pete thank you both for your replies.

Max - Your method works great however if I continue to add formulas to
the worksheet I would need to keep repeating this each time. Is ther a
way to automate this so that if I add a formula to a cell it will
automatically format the text?

Pete - To use conditional formatting, what formula works best to test
if a cell is a formula or not?
 
D

Dave Peterson

You can create a userdefined function that returns true or false if the cell
contains a formula:

Option Explicit
Function HasFormula(rng As Range) As Boolean
Set rng = rng.Cells(1)
HasFormula = rng.HasFormula
End Function

Then you can include that test in your formula:

=hasformula(a1)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
G

Gord Dibben

Try this UDF in your CF.

Function IsFormula(cell)
Application.Volatile
IsFormula = cell.HasFormula
End Function

Copy/paste to a general module then select all cell on your worksheet and

In CF Formula is: =IsFormula(A1)


Gord Dibben MS Excel MVP
 
M

Max

qwopzxnm said:
.. Max - Your method works great however if I continue to
add formulas to the worksheet I would need to
keep repeating this each time. Is there a way to
automate this so that if I add a formula to a cell it will
automatically format the text?
Click to expand...

Another option to tinker with ..

We could assign the Sub FormatFormulaCells()
below to a shortcut key

(I recorded* a macro as the earlier steps were done manually,
the F5 > Special ..., Format > Cells > Font > dark blue/bold ...
then slightly edited the recorder's output)
*via: Tools > Macro > Record New Macro

To install the sub:
In Excel,
Press Alt+F11 to go to VBE
Click Insert > Module
Copy & paste the sub into the code window
Press Alt+Q to get back to Excel

To assign the sub to a shortcut key:
In Excel,
Press Alt+F8 to bring up the Macro dialog
Select "FormatFormulaCells" > click Options,
then assign a shortcut key, say: Ctrl+k
Click OK, then dismiss the dialog (press Cancel)

Test it out ... in any sheet with formulas,
just press the shortcut combo: Ctrl+k
and all the formula cells in the sheet
would be accordingly formatted (dark blue/bold)

Adapt to suit ..

'-----
Sub FormatFormulaCells()
'Selects & formats formula cells on activesheet
On Error Resume Next
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.NumberFormat = "0.00"
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.ColorIndex = 5 'dark blue
End With
Selection.Interior.ColorIndex = xlNone
End Sub
'----
 
M

Max

Oops, please delete* this line in the sub
(or remark it, key in an apostrophe (') in front)

..Name = "Tahoma"

(Tahoma's my default font type setting <g>)
 
Top