Yesterday, I was working with an Excel sheet and wanted to calculate a table of values, but be able to override some of the values. To make it obvious which values were calculated and which typed in, I decided to make the calculated values grey and leave the overridden values as the default black text.
Excel is massively flexible, so I didn't think I'd have much trouble – something in the Conditional Formatting arena would do what I needed. To my surprise, although I could do it, the only technique I found (on the j-walk website) relies on an obscure part of Excel that has almost been lost to memory: the XLM macro language, which was superseded by VBA in 1993.
I'm not entirely sure which version of Excel is in the picture on that page (97?), but it's old enough so that some of the technique has changed. For Excel 2007 (and probably later versions):
- On the "Formulas" ribbon, click "Define Name"
- Name your name, "CellHasFormula"
- In the "Refers to:" box, type, "=GET.CELL(48,INDIRECT("rc",FALSE))"
- Click "OK"
- Select the cells for which you want the conditional formatting rule to apply
- On the "Home" ribbon, click "Conditional Formatting" and select "New Rule…" from the menu.
- Select rule type "Use a formula…"
- In the rule description formula box, type, "=CellHasFormula"
- Change your formatting to the desired style using the "Format…" button
- Press "OK"
In all cases, inverted commas are not included in the values to be used. Be careful of typos: if you make a mistake you probably won't get any error messages (it just won't work).
For the curious, there is a description of the mechanics on the source site. The important point is that "48" is a magic number which instructs the GET.CELL() function to identify cells which contain formulae. Unfortunately, "=GET.CELL(…)" is not a valid argument to the Conditional Formatting rules engine but is to the Define Name engine. It's worth noting that Conditional Formatting rules also don't allow searching for '=' at the start of a cell (anywhere but the start is fine), which would have made this a lot easier.
A complication with using Excel 2007 upwards is that the standard file type (with the .xlsx extension) does not support macros because of potential security issues with passing around files in which they're used. You'll need to use the macro-enabled .xslm format instead. Template files in the .xltm format are fine. When reloading, you may also be told that macros have been disabled. If so, click "Options" on the message to remedy the situation and hit F9 to refresh the formatting once you've done so.
Does anyone know of a better way? Please let me know!
