Comparing two cells not working as expected

Question

I'm working on an application to compare two lists: one Excel-workbook contains revision 1 of a list and a second workbook contains revision 2 of that same list. Both lists have the same structure, meaning: they have the same info in the columns: e.g. column A is always the primary key PK, column B is the temperature, column C is the pressure, etc. There are no formulas in the cells The goal is to find the cells in the New list that are different from the same cell in the old list. When for PK in the old list the temperature = 45 and in the new list the temperature = 50, the cell in the new list will be highlighted in yellow. That makes it easier to find the updates in a list containing 2000 * 120 cells.

It works well with two test-files, but when I try with the real lists I see weird behaviour: in some columns the cells in both lists are empty, but my app still identifies them as different and marks them yellow.

Here is the code I use to loop thru the lists:

    public void Run(int i)
    {

        wsOldSheet = oldWorkBook.Sheets[OldSheetName];

        // Define the range where to search
        PrimaryKeys = wsOldSheet.get_Range(RangeStart, RangeEnd);

        if (EzDiff.Program.Logging == true)
        {
            EzDiff.Program.__logger.Info(".Started working on row on: " + i);
        }

        CurValue = wsNewSheet.Cells[i, ColumnPK].value;
        if (EzDiff.Program.Logging == true)
        {
            EzDiff.Program.__logger.Info("..Primary key = " + CurValue.ToString());
        }

        //1. Check if PK exists in mydata. If not: it's a new PK -> we mark it as new and continue with the next PK

        // Find
        firstFind = PrimaryKeys.Find(CurValue, missing,
                        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                        missing, missing);

        if (firstFind == null)
        {
            if (EzDiff.Program.Logging == true)
            {
                EzDiff.Program.__logger.Info("...Primary key was not found.");
            }
            wsNewSheet.Cells[i, ColumnPK].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
        }
        else
        {
            FoundInRow = firstFind.Row;

            if (EzDiff.Program.Logging == true)
            {
                EzDiff.Program.__logger.Info("...Primary key was found in row: " + FoundInRow);
            }

            for (int mCol = 1; mCol < MaxColumnToWork; mCol++)
            {

                if (wsOldSheet.Cells[FoundInRow, mCol].Value == null)
                //if (String.IsNullOrEmpty(wsOldSheet.Cells[FoundInRow, mCol].Value.ToString()))
                {
                    if (!(wsNewSheet.Cells[i, mCol].Value == null))
                    //if (String.IsNullOrEmpty(wsNewSheet.Cells[i, mCol].Value.ToString()))
                    {
                        // * * Here the cells are marked in error! * * //
                        wsNewSheet.Cells[i, mCol].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                    }
                }
                else
                {
                    if (wsNewSheet.Cells[i, mCol].Value == null)
                    //if (String.IsNullOrEmpty(wsOldSheet.Cells[FoundInRow, mCol].Value.ToString()))
                    {
                        wsNewSheet.Cells[i, mCol].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                    }
                    else
                    {
                        String strOld = wsOldSheet.Cells[FoundInRow, mCol].Value.ToString();
                        String strNew = wsNewSheet.Cells[i, mCol].Value.ToString();

                        if (strNew.CompareTo(strOld) != 0)
                        {
                            wsNewSheet.Cells[i, mCol].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                        }
                    }
                }

            }

            if (EzDiff.Program.Logging == true)
            {
                EzDiff.Program.__logger.Info("....finished comparing all columns from row: " + FoundInRow);
            }
        }
    }

If anyone can see where I go wrong, pls let me know!

** SOLVED ** When I dug in deeper and looked at the cells causing the weird results, I noticed that these celes were NULL in the one sheet and "" (empty) in the other sheet. So I solved it with this:

        private bool equiv(object obj1, object obj2, double tolerance)
    {
        if ( ((obj1 == null) && (obj2 == null)) ||
            ((obj1 == null) && (obj2 == "")) ||
            ((obj1 == "") && (obj2 == null)) )
        {
            return true;
        }
        else if ((obj1 == null) || (obj2 == null))
        {
            return false;
        }
    }

Maybe not pretty, but if it does the job, I'm happy.

Answer 1

In VBA you can also use tests like:

if isempty(wsOldSheet.Cells(FoundInRow, mCol)) then ...

'or 

if wsOldSheet.Cells(FoundInRow, mCol) is nothing then ...

Answer 2

In Excel, blank cells return zero-length string, not null. Try replacing instances like:

if (wsOldSheet.Cells[FoundInRow, mCol].Value == null)

with:

if (wsOldSheet.Cells[FoundInRow, mCol].Value == "")

and see if that helps. The VBA equivalent would be:

If wsOldSheet.Cells(FoundInRow, mCol).Value = "" Then
   ...
End If

which I mention because I am not a C# programmer, but I know that this works in VBA.