Instead of a Macro you can perform in these steps in Excel 2007.
Click on cell A1
- While holding Shift & Control down, click on End button. (The idea is to select the entire worksheet).
- Now on Home Tab Click Conditional Formatting
- On Highlight Cell Rules … .select Equal To
- On Format Cells that are EQUAL TO … Type 999999; then select the Format/Color from the Drop Down list. Or select Custom & then select the Gray color that you wish.
I cut and pasted your macro into VB Editor in Excel 2007, tidied up indents etc and assigned it to a hot key. It worked perfectly!!