coloring cells that contain formula

[Jack Sons]

Hi all,

In a website I found this:
---------------------------------------------------------------

At some time, you may wish to add color to all the cells in your worksheet
that contain formulas by coloring those cells. Follow these steps:
It is interesting to note that you don't have to create a VBA macro to use
the conditional formatting route, if you don't want to. Instead, you can
follow these steps if you are using a version of Excel prior to Excel 2007:

1.. Press Ctrl+F3. Excel displays the Define Name dialog box.
2.. In the Names field (at the top of the dialog box), enter a name such
as FormulaInCell.
3.. In the Refers To field (at the bottom of the dialog box), enter the
following:
4.. =GET.CELL(48,INDIRECT("rc",FALSE))
5.. Click OK.
---------------------------------------------------------------
A few questions:

What is the Dutch language version (translation) of the formula in line 4?

I do not understand how the first argument of CELL can be a number. I
thought it should be an infotype between "", like "notation" or "row"
(translated from Dutch).

When I substitute the "rc" with the Dutch "R1K1" the part
INDIRECT("rc",FALSE) seams to result (with F9) always in the value of cell
A1. I don't understand that either.

In fact I don't understand anything (!) of the formula in line 4.
I would appreciate if anybody would shed light on it.

Jack Sons
The Netherlands

 

[Bob Phillips]

=CEL.LEZEN(48,INDIRECT("rc",FALSE))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary''s Student]

This might be easier to translate into Dutch:

Sub colorFormulas()
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
r.Interior.ColorIndex = 6
End If
Next
End Sub

 

[Shane Devenshire]

Hi,

I have no idea about the dutch translation, hope Bob did that for you.

However, what is going on here is the following:

The Excel 4.0 macro language contained a function =GET.CELL("thing",ref) it
is not a spreadheet function, and microsoft doesn't document it any longer in
their help system, however you can download a help file for these functions.

This particular function returns information ("thing") about the CELL at
location ref. It is very much like the speadsheet function
CELL("filename",A1). However, it allows many more things to be returned than
the spreadsheet CELL funtion and you indicate which thing you want returned
with its number. 48 is the code for the hasformula argument.

 

[Jack Sons]

Bob, GS and Shane,

Thank you all.
To my regret I can't find GET.CELL (Dutch CEL.LEZEN) in the help in Excel
2k.

Jack.

 

[Bob Phillips]

Jack,

It is documented in a file called macrofun.hlp. You might be able to google
it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)