coloring cells that contain formula
 

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

coloring cells that contain formula
 

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

--
HTH

Bob

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

"Jack Sons" wrote in message
...
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

coloring cells that contain formula
 

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

--
Gary''s Student - gsnu2007L

coloring cells that contain formula
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jack Sons" wrote:

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

coloring cells that contain formula
 

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.

"Jack Sons" schreef in bericht
...
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

coloring cells that contain formula
 

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)

"Jack Sons" wrote in message
...
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.

"Jack Sons" schreef in bericht
...
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