Chapter 7 continued ...
| |
Tip – Quickly delete the formula without deleting the calculation resultSelect a cell containing a formula, press F2 and then press F9. |
Deleting formulas and pasting values using a single line of code and a keyboard shortcut
Record a macro adding a Shortcut Key and save the macro in a Personal workbook. Enter the following code to the macro you created:
Selection.Formulas=Selection.Value
Use the Go To dialog box to select formulas in a sheet to delete, to
protect (see below), or to color.
1. Press F5, or from the Edit menu, select Go to.
2. In the Go to dialog box, click Special.
3. Select Formulas.
4. Click OK.
Only cells that contain formulas are selected in the sheet.
Identifying and Formatting Cells with Formulas
Excel does not provide a formula that identifies formulas. VBA has a function called HasFormula. The solution is to create a custom function (see explanation at end of chapter) to identify a cell containing a formula.
Function FormulaInCell(Cell) As Boolean
FormulaInCell = Cell.HasFormula
End Function
Use the technique described below to combine the Get.Cell formula with conditional formatting to format cells containing formulas. After creating the formula FormulaInCell, combine it with Conditional Formatting.
Combining the Get.Cell formula and conditional formatting to format a cell with a formula
Excel includes the macro language XLM. This macro language has a function called Get.Cell. Use this function to identify a formula in a cell by combining it with conditional formatting. The technique is described below.
1. Select a cell in the sheet, and press Ctrl+F3.
2. In the Define Name dialog box, type the name FormulaInCell.
3. Type the formula =GET.CELL(48,INDIRECT(“rc”,FALSE)) in the Reference field.
4. Select all the cells in the sheet by pressing Ctrl+A.
5. From the Format menu, select Conditional formatting.
6. In Condition 1, select Formula is.
7. In the formula box, type =FormulaInCell.
8. Click Format.
9. From the Font tab, select the color yellow, and click OK.
10. Click OK.
