Hi,

I am trying to create an add-in that will identify cells with formulae that have a different formula €˜structure to those cells around it (i.e. The 4 adjacent cells surrounding this cell- I wont have any cells in the first row or column to worry about). As most formulae in a list are either copied down or copied across (or both), I am aiming to identifying the cells with €˜fudged/manually edited formulae.

For example, say that all cells within a range A1:E10 except B3 contains a SUM function (say range A1 has €œ=SUM(F1:H1)€� and then copied across and down), whereas cell B3 might have been manually changed to €œ=B2€�.

Is it possible to identify B3 as a fudged cell using VBA (after which Ill set its font colour to red)?

The approach that Ive thought of is, for each cell in Specialcells(xlCellTypeFormulas), compare it with the 4 cells to the left and right and top and bottom of it, and make its font red if it has a different formula structure to, say, more than 2 of these cells (to allow for there being 2 fudged cells in proximity).

I am using Excel 2000.

Thankyou so much for your help in advance!

SuperJas.

This will identify the fudged cells, but it might highlight more than you
want. You may just want to detect whether "most" of the cells around it
differ? You'll see what I mean.

Sub test()
Dim lngLastRow As Long, lngLastCol As Long, rng As Range

With ActiveSheet
'Get last row/col
On Error Resume Next
lngLastRow = 1: lngLastCol = 1
With .UsedRange
lngLastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
lngLastCol = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
End With

If lngLastRow 1 And lngLastCol 1 Then
For Each rng In Range(.Cells(2, 2), .Cells(lngLastRow,
lngLastCol))
If Not (rng.Formula = rng.Offset(-1, 0).Formula And _
rng.Formula = rng.Offset(1, 0).Formula And _
rng.Formula = rng.Offset(0, -1).Formula And _
rng.Formula = rng.Offset(0, 1).Formula) Then
rng.Interior.Color = vbRed
End If
Next
End If
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"SuperJas" wrote in message
...
Hi,

I am trying to create an add-in that will identify cells with formulae
that have a different formula 'structure' to those cells around it (i.e. The
4 adjacent cells surrounding this cell- I won't have any cells in the first
row or column to worry about). As most formulae in a list are either copied
down or copied across (or both), I am aiming to identifying the cells with
'fudged'/manually edited formulae.

For example, say that all cells within a range A1:E10 except B3 contains a
SUM function (say range A1 has "=SUM(F1:H1)" and then copied across and
down), whereas cell B3 might have been manually changed to "=B2".

Is it possible to identify B3 as a fudged cell using VBA (after which I'll
set it's font colour to red)?

The approach that I've thought of is, for each cell in
Specialcells(xlCellTypeFormulas), compare it with the 4 cells to the left
and right and top and bottom of it, and make its font red if it has a
different formula structure to, say, more than 2 of these cells (to allow
for there being 2 fudged cells in proximity).

I am using Excel 2000.

Thankyou so much for your help in advance!

SuperJas.