distinguishing formula cells

A

analyst41

I have a column where most cells are based on a formula; but from time
to time I would override selected cells with a plain number.

Is there a way to automatically show (say with a shading) which cells
in a column are based on formulas and which cells are directly entered
numbers (the ability to do either one is sufficient, but I'd like to
know how to do both) ?

Thanks in advance - its amazing how much help one gets in this ng.
 
R

Ragdyer

You could try the elemental "Go To".

Select the range (or the entire sheet) that you'd like to examine.

Hit <F5>,
Click on "Special",

Then, click on <Formulas> <OK> to have all formula cells highlighted,
OR ...
Click on <Constants> <OK>, to have the other data cells highlighted.
 
A

analyst41

Ragdyer said:
You could try the elemental "Go To".

Select the range (or the entire sheet) that you'd like to examine.

Hit <F5>,
Click on "Special",

Then, click on <Formulas> <OK> to have all formula cells highlighted,
OR ...
Click on <Constants> <OK>, to have the other data cells highlighted.
Click to expand...

Thanks that works temporarily(if I click elsewhere the shadings
disappear) ; I want to make the shading of non-formulas permanent. is
there a way to make that happen ?
 
C

Chip Pearson

Once the cells are selected, as Ragdyer illustrates, you can
click the Fill button on the Formatting toolbar to change the
background color of the cells.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
D

Dave Peterson

You could use Format|Conditional formatting and a UserDefined Function.

In a General Module:

Option Explicit
Function IsFormula(rng As Range)
IsFormula = rng(1).HasFormula
End Function

Then back to excel and use this in your Format|conditional formatting rules:

Formula is: =isformula(A1)

(If A1 is the activecell.)

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

analyst41

Chip said:
Once the cells are selected, as Ragdyer illustrates, you can
click the Fill button on the Formatting toolbar to change the
background color of the cells.
Click to expand...

thank you sooo much.
 
A

analyst41

Dave said:
You could use Format|Conditional formatting and a UserDefined Function.

In a General Module:

Option Explicit
Function IsFormula(rng As Range)
IsFormula = rng(1).HasFormula
End Function

Then back to excel and use this in your Format|conditional formatting rules:

Formula is: =isformula(A1)

(If A1 is the activecell.)

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

looks a bit advanced for me; I'll file it away and play with it later.
Thanks.
 
Top