2

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.

Kersijus
  • 87
  • 1
  • 7
  • what does not work? I dont understand your question. `Errors` is a read-only property so you can't set the values there –  Jul 01 '14 at 13:38
  • The condition IF will only execute "oneCell.Interior.ColorIndex = xlNone" even if there are cells with Inconsistent Formulas in the Used Range. – Kersijus Jul 01 '14 at 13:44
  • 1
    That does not sound right. What is the `ActiveSheet.UsedRange.Addess`? –  Jul 01 '14 at 13:45
  • 1
    It works well for me. I have one inconsistent formula on my sheet and it works perfectly. – D_Bester Jul 01 '14 at 13:45
  • `oneCell.Errors(xlInconsistentFormula).Value` will be `TRUE` if the cell is inconsistent – D_Bester Jul 01 '14 at 13:46
  • D_Bester > I'm using Excel 2010, does that make any change? I will reboot it and rerun the code. mehow > The Address of used range is A1:BK199. I've tried with Application.Selection to reduce the range but didn't make any change. – Kersijus Jul 01 '14 at 13:51
  • Do you really have any inconsistent formulas? – D_Bester Jul 01 '14 at 14:02
  • @D_Bester Ok, now this is getting weird. I've tried the code on the sheet I'm using and it didn't work (even when converting to .xls) but when I put the code on a new sheet it works fine. I don't get it. Edit: Does it make any difference if the formula is incoherent with the calculated column of a table? – Kersijus Jul 01 '14 at 14:03
  • @D_Bester Yes I have incoherent formulas with the Calculated Column formula. Excel propose to restore the incoherent formulas with the column formula. – Kersijus Jul 01 '14 at 14:07
  • Well I've seen Excel be weird before so I don't know what else to say. – D_Bester Jul 01 '14 at 14:08
  • Thank you anyway for your time and help. – Kersijus Jul 01 '14 at 14:14
  • I am using Excel 2010, this code works as I expect. – David Zemens Jul 01 '14 at 14:15
  • It works when you have a normal range, but if you have a table and Excel shows the error "This cell is inconsistent with the column formula" it doesn't. – Kersijus Jul 01 '14 at 14:23

2 Answers2

3

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    This doesn't work when the used range is a table and Excel shows the error "This cell is inconsistent with the column formula". Also changing the table back to a normal range won't be much good as this will show errors for inconsistent formulas between columns. – Kersijus Jul 01 '14 at 14:25
  • @Kersijus ...........your comment is very interesting....when I convert a simple column into a **Table** .........the green error flag vanishes and the code cannot detect the error! – Gary's Student Jul 01 '14 at 14:40
  • Yes, it seems that Excel acts weird with table. However, when you add a table before putting the formulas it will fill all the column with the same formula. When this happens, you can edit a specific cell and undo the automatic fill. This will give you an Inconsistent Formula, but the code won't work unfortunately. – Kersijus Jul 01 '14 at 15:11
0

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).

D_Bester
  • 5,723
  • 5
  • 35
  • 77
  • I think it's not exactly that, it says something like “This cell is incoherent with the column formula” (I don't work in English so sorry if translation is bad). – Kersijus Jul 01 '14 at 14:09
  • In English it does use the term "Inconsistent formula" and I don't think that "incoherent" is a good translation. – D_Bester Jul 01 '14 at 14:33