add data but don't delete data that has no match

[VbaHell]

Hello all

I have an issue that I hope someone can help with

In Column "U" I have existing Data which is "Won" and "Outstanding"
In column "V" I have a drop down list but at the moment the data is blank
How can I have it that if I select data in column "V" that column "U" would then change to "Lost"
If there is no change in rows on column "V" then the existing data in rows column "U" would stay the same.

I need this as VBA please because it will be part of some code that I have to merge this information

 

[Osvaldo Palmeiro]

Hi. Try this code into the appropriate sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error Resume Next
 If Target.Column <> 22 Or Target.Value = "" Then Exit Sub
 Target.Offset(, -1).Value = "Lost"
End Sub

 

[VbaHell]

Hi Osvaldo

Thank you very much for the reply, this works great but I should have mentioned that the changes are bought in as a merged file so the worksheet changes event will not make the changes, is there way I can add lines of code to my merge file to make these changes please

This is the code I am using for the merge

Public Sub ConsolidateUpdates()

    Dim FilePath As Variant
    Dim WkBook As Workbook
    
    Application.ScreenUpdating = False
    
    Application.FileDialog(msoFileDialogOpen).FilterIndex = 3
    Application.FileDialog(msoFileDialogOpen).Show
    
    For Each FilePath In Application.FileDialog(msoFileDialogOpen).SelectedItems
        Set WkBook = Application.Workbooks.Open(CStr(FilePath))
        
        ConsolidateWkBook WkBook
        
        WkBook.Close False
    Next
Application.ScreenUpdating = True
End Sub
Public Sub ConsolidateWkBook(WkBook As Workbook)
    
    Dim UpdatedSheet As Worksheet, LocalSheet As Worksheet
    Dim CurrentSheetRow As Long, i As Long
    
    Application.ScreenUpdating = False
    
    On Error GoTo err_handler
    
    
    Set UpdatedSheet = WkBook.Worksheets(1)
    Set LocalSheet = ThisWorkbook.Worksheets("Data")
    
    For i = 2 To UpdatedSheet.Cells(UpdatedSheet.Rows.Count, 1).End(xlUp).Row
        
        If UpdatedSheet.Range("A" & i) > "" Then
            CurrentSheetRow = LocalSheet.Columns("A:A").Find(What:=UpdatedSheet.Range("A" & i), LookAt:=xlWhole).Row
            
            UpdatedSheet.Rows(i).Copy
            LocalSheet.Rows(CurrentSheetRow).PasteSpecial xlValues
        End If
    Next
    
    
exit_handler:
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Exit Sub
    
err_handler:
    If Err.Number = 91 Then
        CurrentSheetRow = Sheet1.LastRow + 1
        Resume Next
    Else
        MsgBox Err.Number & vbNewLine & Err.Description, vbInformation, "Error"
        Resume exit_handler
    End If
    Sheets("Data").Columns("X:X").ClearContents
    
End Sub