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

[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 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.

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

[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

[Max]

"qwopzxnm" wrote:
> 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.

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 ...

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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?

[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



qwopzxnm wrote:
>
> 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?
>
> --
> qwopzxnm
> ------------------------------------------------------------------------
> qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
> View this thread: http://www.excelforum.com/showthread...hreadid=529671

--

Dave Peterson

[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

On Tue, 4 Apr 2006 12:18:20 -0500, qwopzxnm
<[email protected]> wrote:

>
>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?

[Max]

"qwopzxnm" wrote:
> .. 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?

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
'----
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---