Checking if a formula is consistent from top to bottom of a column

**Bonnister** · 07-11-2012, 09:23 AM

Hello folks

Is there a formula or bit of VBA wizardry that will allow me to quickly check whether a formula is consistent all the way down a column? So for example, a number of the files I work on on a daily basis will contain multiple columns of formulae, and if I am new to the file it's not easy to spot where another user has keyed over a cell with a text string, which can cause problems further down the line

All help is appreciated as usual

Thanks
Kenny

**Fotis1991** · 07-11-2012, 09:31 AM

Hi

An idea is to highlight using CF, all the cells that includes formulas. Will this work for you?

**dilipandey** · 07-11-2012, 09:33 AM

Hi Kenny,

If I understand correctly there are two aspects to it :-

1) If a formula has been keyed over with a text string i.e, formula converted to constant value and,
2) If all the formulas of a particular column(s) are consistent

for 1) -> press F5 -> Special -> Formula and OK... you'll see all the formulas in the worksheet highlighted (selected ) and you can easily check which cell is missing formula

for 2) -> press CTRL+` i.e, the key combination of Ctrl + (the key above tab key) and you'll find the formula appearing instead of their calculated cells, now you can easily get to see where the formula is changed.
Also, a smart tag appears on the top of the cell boarder, where the formula is not consistent

Regards,
DILIPandey

<click on below 'star' if this helps>