First, I'd use .formulaR1C1 instead of .Formula. Excel/VBA can forgive you, but
doesn't have to.

Second, I'm not sure what column/formula returns the error or difference
indicator, but maybe you could use test like:

if application.countif(.range("x1:x" & lastrow), "yourindicator") 0 then
'found a difference
'what should happen

You may need to add another column or change one of the formulas to return a
nice indicator that you can search for. (I told you I'm confused!)

Jim G wrote:

I have a hidden sheet that compares the two lists to find additions in a new
list compared with the current list and displays the result in the first rows
of an adjacent column. Is there a way to check if differences are listed in
column D and if so, display a pop up message to alert the user to take action
in updating the master list? I want this to be part of a larger routine, so
if there are no differences then continue with the existing proceedure.

Sub CreateFormulaCols()
' will compare two lists and display the resilts at the top of the first page
'Formulas are copied to the last row with data in Col B. This should be the
exhaustive set

With Worksheets("RefList")
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("c1:C" & Lastrow).Formula =
"=IF(RC[-2]="""","""",IF(ISNUMBER(MATCH(RC[-2],C[-1],0)),"""",ROW()))"
.Range("D1:D" & Lastrow).Formula =
"=IF(ISERROR(SMALL(C[-1],ROW(RC[-3]))),"""",INDEX(C[-3],MATCH(SMALL(C[-1],ROW(RC[-3])),C[-1],0)))"
.Range("E1:E" & Lastrow).Formula =
"=IF(RC[-3]="""","""",IF(ISNUMBER(MATCH(RC[-3],C[-4],0)),"""",ROW()))"
.Range("F1:F" & Lastrow).Formula =
"=IF(ISERROR(SMALL(C[-1],ROW(RC[-5]))),"""",INDEX(C[-4],MATCH(SMALL(C[-1],ROW(RC[-5])),C[-1],0)))"

End With
End Sub
--
Jim

--

Dave Peterson