CODEWITHSUNDEEP
vbaexcel
Kersijus
Kersijus

Reputation: 87

Find inconsistent formulas in Excel through VBA

Excel detects if a formula is inconsistent with a calculated column in a table and shows them with a little green triangle (which says "This cell is inconsistent with the column formula"). How can I find them through VBA. I found this code that is supposed to do the trick (from http://www.ozgrid.com/forum/showthread.php?t=145306) but this only works when the used range is a normal range instead of a table:

Dim oneCell As Range 

For Each oneCell In ActiveSheet.UsedRange 
    If oneCell.Errors(xlInconsistentFormula).Value Then 
        oneCell.Interior.ColorIndex = 6 
    Else 
        oneCell.Interior.ColorIndex = xlNone 
    End If 
Next oneCell

But oneCell.Errors(xlInconsistentFormula).Value only sends "False" so it doesn't work.

Can this be fixed to work in a table instead of a normal range?

Edit: xlInconsistentFormula doesn't do anything if you work in a table.

Upvotes: 2

Views: 3736

Answers (2)

Gary's Student
Gary's Student

Reputation: 96771

This works:

Sub fhdjksjdfhs()
    Dim r As Range
    Dim rBig As Range
    Set rBig = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
    For Each r In rBig
        If r.Errors.Item(xlInconsistentFormula).Value = True Then
            r.Interior.ColorIndex = 6
        Else
            r.Interior.ColorIndex = xlNone
        End If
    Next r
End Sub

EDIT#1:

As Kersijus correctly points out, converting a column or set of columns into a Table suppresses the level of error-checking that raises the green flag. This code will not detect errors that are suppressed in this way.

Upvotes: 3

D_Bester
D_Bester

Reputation: 5921

If you have an inconsistent formula you should see something like this:

enter image description here

If you have none like this you might have notifications turned off (I think that's possible).

Upvotes: 0

Related Questions