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

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)


--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
View this thread: http://www.excelforum.com/showthread...hreadid=529671

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

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

Stumped: If a cell contains a formula, can you make text color aut
 

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

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

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

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

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

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

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

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

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

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

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