Cell Comparison

I’d be grateful if anyone can offer some advice on how to achieve my goal by either modifying the code below or offering an alternative method.

The spreadsheet named “Register Rev e Forum Post” will be updated routinely, only one worksheet at a time, to show the actual status ‘OPEN’ or ‘CLOSED’.

What I need to do is when a particular actual (cell D) status differs from the normal (cell C) status then the whole row needs to be copied to another workbook (Out of Sequence). Any subsequent differences will be added to the next available row. If any of these discrepancies revert to the correct status (ie Normal & Actual are the same) then the associated row on the ‘Out of Sequence’ worksheet will be deleted.

The macro in ‘This Workbook’ of ‘Register Rev e Forum Post’ achieves this somewhat but has the following bugs.

• Rows are copied only to rows 4 & 5 on the ‘Out of Sequence’ worksheet. For example the first two differences will be copied to these rows then if a third existed it would copy into row 5 and hence only show 2 selections when 3 actually existed.

• This macro requests that the input be in uppercase with no background colour. In reality these inputs will be as seen in the Normal columns ie red or green background.

• This macro requires the ‘Out of Sequence’ workbook to be open. Ideally I would like to have this workbook, which will be located in another drive, to be copied to automatically without the need to manually open the workbook prior to the input of data to ‘Register Rev e Forum Post’ workbook.
There is also no need for the error message regarding the input data.

I have very limited knowledge of coding and have been reliant so far on the help of others. Any help at all would be much appreciated.

I'm using Excel 2007

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'capture out of sequence valves on entry of Normal or Actual position
'assume Out of Sequence register workbook is open
    Dim NormalValve As String
    Dim ActualValve As String
    Dim RegisterNbr As String
    Dim wbOutOfSeq As Workbook       ' Out of Sequence workbook
    Dim wsOutOfSeq As Worksheet      ' Sheet in Out of Sequence workbook
    Dim OutOfSeqValveNbrs As Range   'register numbers in Out of Sequence workbook
    Dim FoundRegisterNbr As Range
    Dim NextRow As Long
 
    'On Error GoTo ErrorRoutine  'if any error occurs goto the error routine
 
    'Target must be a single cell.
    If Target.Count > 1 Then GoTo WrapUp
 
    'Target value must be below row 3
    If Target.Row < 4 Then GoTo WrapUp
    'if changed data was the normal or actual valve position edit entry
    If Not Intersect(Target, Range("C:D")) Is Nothing Then
 
        'assure entry is upper case and no cell color
        Application.EnableEvents = False    'to prevent refiring of this procedure
        Target = UCase(Target)
        Target.Interior.ColorIndex = xlNone
 
        'check if entry is "Open" or "Closed"
        If Target <> "OPEN" And Target <> "CLOSED" Then
            Target.Interior.ColorIndex = 36
            Target.Select
            MsgBox "WARNING! Valve position must be 'OPEN' or 'CLOSED'." _
                 , vbExclamation, "Valve Position Error"
            Target = "XXXX"
        End If
    End If
 
    'create workbook object
    Set wbOutOfSeq = Workbooks("Out of Sequence.xls")
 
    'create a sheet object
    Set wsOutOfSeq = wbOutOfSeq.Sheets("Out of Sequence")
 
    'create range object of out of sequece register numbers
    Set OutOfSeqValveNbrs = wsOutOfSeq.Range("B2", wsOutOfSeq.Range("B65536").End(xlUp))
 
    'actual valve position is in column 4
    ActualValve = Cells(Target.Row, 4)
 
    'normal valve position is in column 3
    NormalValve = Cells(Target.Row, 3)
 
    'register number is in column 2
    RegisterNbr = Cells(Target.Row, 2)
 
    If ActualValve <> NormalValve Then  ' out of sequence so do the following
 
        'check if register number already in Out of Sequence register.
        'FoundRegisterNber is the range address of the register number if found in the tregister
        Set FoundRegisterNbr = OutOfSeqValveNbrs.Find(RegisterNbr, LookIn:=xlValues, lookat:=xlWhole)
 
        If FoundRegisterNbr Is Nothing Then    'add out of sequence to the register
 
            'find the next empty row
            NextRow = wsOutOfSeq.Range("A4", wsOutOfSeq.Range("A65536").End(xlUp)).Row + 1
 
            'copy out of sequence valve data to out of sequnece register
            Target.EntireRow.Copy wsOutOfSeq.Cells(NextRow, 1)
 
        Else    ' record found, update register
 
            'copy out of sequence valve data to out of sequnece register
            Target.EntireRow.Copy wsOutOfSeq.Cells(FoundRegisterNbr.Row, 1)
        End If
 
    Else    'if not out of sequence then delete from out of sequence register if in register
        Set FoundRegisterNbr = OutOfSeqValveNbrs.Find(RegisterNbr, LookIn:=xlValues, lookat:=xlWhole)
        If FoundRegisterNbr Is Nothing Then GoTo WrapUp
        FoundRegisterNbr.EntireRow.Delete
    End If
WrapUp:
    Application.EnableEvents = True
    Exit Sub
ErrorRoutine:
    MsgBox Err.Number & "  " & Err.Description, vbCritical, "Error"
    GoTo WrapUp
End Sub