Microsoft Office Tutorials and References
In Depth Information
Using XLM Get.Cell Functions
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run “DeleteDialog”
blnMessage = True
ThisWorkbook.Save
End Sub
using xlM gET.cEll funcTions
Get.Cell is a function from the Excel 4.0 Macro Language (XLM) that returns information about
the formatting, location, or contents of a cell. The syntax of the Get.Cell function is Get.Cell(num,
cell reference) , where the num argument may be one of 66 numbers that correspond to the piece of
information being sought.
As an example of how you can benefit from a Get.Cell function, suppose you have a worksheet
that contains formulas, text, and empty cells. You can apply Conditional Formatting to the range
with the Get.Cell function and its number 48 number argument, which evaluates a cell for the
existence of a formula. The following steps lead you through the process, and when completed, your
cells will be conditionally formatted in real time to reflect the existence of a formula, or a constant
value, or nothing.
1.
Press the Ctrl+F3 keys to insert a new name.
2.
Depending on the version of Excel you are using, in the Names in Workbook field or the
Name field, enter the word Formulas . (You can enter most any name you want, but to keep it
simple just call it Formulas.)
3.
In the Refers To field near the bottom of the dialog box, enter
=GET.CELL(48,INDIRECT(“rc”,0)) .
4.
Click Add, then click OK.
5.
Select the range of cells on your worksheet that you want to conditionally format. For this
example, select cell A1 to the last row and column of your choice.
6.
Open the Conditional Formatting dialog box. If you are using a version prior to Excel 2007,
from the menu bar click Format Conditional Formatting, and select Formula Is from the
drop-down menu. If you are using a later version, click the Home tab on the Ribbon, click
the Conditional Formatting icon, and click New Rule Use a Formula to Determine Which
Cells to Format.
7.
The first Conditional Formatting formula to be entered is =Formulas . After that, select your
ill color for formula-containing cells and click OK.
8.
While still in the Conditional Formatting dialog box, click the Add button.
9.
Enter a second formula for text-containing cells, which is =AND(LEN(A1)>0,ISTEXT(“rc”)) .
10.
Click the Format button, select the kind of formatting you want for cells containing
constants, and then click OK. Click OK again to exit the Conditional Formatting dialog box.
Search JabSto ::




Custom Search