Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coloring the cells by formula | Excel Discussion (Misc queries) | |||
coloring cells | Excel Discussion (Misc queries) | |||
COLORING IN CELLS | Excel Discussion (Misc queries) | |||
Coloring certain cells | Excel Worksheet Functions | |||
Coloring Cells | Excel Worksheet Functions |