private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }
                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);
                

Select all Open in new window

Option Explicit

Sub myActivate(anyRng)
    If Intersect(Selection, anyRng.Cells(1)) Is Nothing Then
      ' if rng's first cell does NOT overlap current selection, then rng.select and rng.activate are identical
        anyRng.Select
    Else
       ' otherwise, if rng's first cell DOES overlap current selection, that cell is activated
        anyRng.Select
        anyRng.Cells(1).Activate
    End If
End Sub

Select all Open in new window

Set Wb1 = Workbooks.Open("C:\_Excel\CompareSheets\Book1.xls") 'Target file (gets the red)
Set Wb2 = Workbooks.Open("C:\_Excel\CompareSheets\Book3.xls") 'Master file
Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)
Ws1LR = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
Ws1LC = Ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Set ColRng = Ws1.Range("A2", Ws1.Cells(Ws1LR, Ws1LC))

Select all Open in new window

For Each c In ColRng
    celladdress = c.Address
    If c <> Ws2.Range(celladdress) Then
        c.Interior.ColorIndex = 3
    Else
        c.Interior.ColorIndex = 0
    End If
Next c

Select all Open in new window

Sub CompareTwoSheets()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Ws1LR As Long, Ws1LC As Long
Dim c As Range, ColRng As Range
With Application
'    

Select all Open in new window