color cells with formulas

[Joanna_gr]

Hi again!

I want to apply special formatting to cells that contain formulas.
I think u've already answered this but i don't know where to locate the answer. Can u please help me?
Thanx

[Aladin Akyurek]

On 2002-02-26 02:38, Joanna_gr wrote:
Hi again!

I want to apply special formatting to cells that contain formulas.
I think u've already answered this but i don't know where to locate the answer. Can u please help me?
Thanx

Joanna,

You need a user-defined function which can test whether a cell houses a formula:

VBA is not my PL (neither COBOL, by the way), but you can insert the following code in a module in your WB:

Function HoldsFormula(Cell) As Boolean
HoldsFormula = Cell.Range("A1").HasFormula
End Function

Now you can apply Cond Format to, B1:B10, a range of interest:

Select B1:B10;
Activate Format|Conditional Formatting;
Choose "Formula Is" for Condition 1;
Enter in the formula box:

=HoldsFormula(B1) [ important: don't freeze B1 by using F4 -- that is, no $$ here ]

Activate Format;
Choose a color on the Patterns tab;
Click OK, OK.

Aladin

[Derek]

Someone did post a clever answer to this some time ago. I have forgotten the author so I am sorry I can't give credit. As I remember it, it went:

Select all cells
At the toolbar select Insert,Name, Define
In "Names in Workbook" type "formula" (do NOT use quotation marks)
In "Refers to.." type GET.CELL(48,A1)
with all cells still selected go to Format, Conditional Formatting
Change "Cell value is" to "Formula is" and type in = "formula" (do NOT use quotation marks)
Select a coloured pattern and click OK
This should highlight all cells with formulas
Check conditional formattting again to ensure there are no quote marks around the word formula that you typed in.
regards
Derek

[Joanna_gr]

Thanx Derek, Aladin

Derek, it worked. thank u very much. Can u please explain to me what CELL(48,A1) refers to? thanx again.

[Derek]

Hi Joanna
I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
Good Luck
Derek

[Aladin Akyurek]

On 2002-02-26 05:00, Derek wrote:
Hi Joanna
I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
Good Luck
Derek

Here is a link to get more info:

http://www.j-walk.com/ss/excel/usertips/tip045.htm

[Juan Pablo González]

Derek, thanks... this was me. Posted as a Tip of the Day, for whoever needs it again.

This uses Excel 4 macros (Get.Cell is a "command"), and the 48 is just a parameter... there are a lot of parameters for cells (60 or 70 if i'm correct), and each one has it's own meaning !

http://www.mrexcel.com/weblog/archiv..._webloga.shtml

Tip for Tuesday, Dec 18th. 2001.

On 2002-02-26 05:00, Derek wrote:
Hi Joanna
I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
Good Luck
Derek

[Aladin Akyurek]

I noticed that too late!

On 2002-02-26 05:49, Juan Pablo G. wrote:
Derek, thanks... this was me. Posted as a Tip of the Day, for whoever needs it again.

This uses Excel 4 macros (Get.Cell is a "command"), and the 48 is just a parameter... there are a lot of parameters for cells (60 or 70 if i'm correct), and each one has it's own meaning !

http://www.mrexcel.com/weblog/archiv..._webloga.shtml

Tip for Tuesday, Dec 18th. 2001.

[Autolycus]

I would not recommend using this method of identifying cells with formulas.
Cells that have this conditional formatting cannot be copied and pasted to other worksheets and attempting to do so might cause Excel to crash.

I use these macros to identify formulas and to remove the identification :-


Sub IdentifyFormulae_Add()
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
With ws.UsedRange
'Remove cell highlights (in case there _
are any cells highlighted but without _
formulae)
.Interior.ColorIndex = 0
'Identify Formulae
On Error GoTo e 'Error handler if there are no formula cells
.SpecialCells(xlFormulas).Interior.ColorIndex = 20
End With
Range("A1").Select
Next
Exit Sub
e: MsgBox "There are no cells with formulas"
End Sub

Sub IdentifyFormulae_Remove()
'Remove Identify Formulae (removes all _
cell highlights in the selection)
Selection.Interior.ColorIndex = 0
Range("A1").Select
End Sub

[Aladin Akyurek]

On 2002-02-26 06:29, Autolycus wrote:
I would not recommend using this method of identifying cells with formulas.
Cells that have this conditional formatting cannot be copied and pasted to other worksheets and attempting to do so might cause Excel to crash.

That's interesting. Thanks pointing out.

Aladin