Can the value of one cell update based on if the cell contents of another are deleted or changed?

[Dan2k78]

Hello,

I’ve been trying to work something out for a few days now through googling and reading forum posts but I finally give in and now it's time to ask for some proper help.

I have sheet1 which contains a floor map (a named range) with consignment numbers on it that are added manually by different users. When a consignment is loaded the user then deletes the contents of the cell completely.

On sheet2 I have a list of consignments for the day and a COUNTIF formula tells me if the consignment is out on the floor or not. If the consignment is out it says “Out” and if it isn’t then it says “Not Out”.

At the moment, once the user deletes the contents of a cell “Out” reverts back to “Not Out” as it should with COUNTIF. What I really need to do is make the consignment on sheet2 somehow show as loaded when a user deletes the consignment number/contents from a cell in sheet1.

Something like if the value of a cell changes from ‘Consignment 1’ back to blank on sheet 1 then mark it as loaded on sheet 2 or if “Out” reverts back “Not Out” when a cell is cleared then show it as loaded on sheet2. Anything?

On image 4 ideally something would have logged the change spmewhere and marked it as 'loaded' when the status changed back to “Not Out”. This way the loaded consignments could be periodically filtered and their rows deleted until everything was loaded.

Any help would be greatly appreciated as I’m stumped with this one.

Thanks.

 

[HighAndWilder]

Give this a go.

Put the code in the Sheet1 code module.

I am assuming that the Route,Status and Loaded columns are in A, B and C. of each sheet.

Are you able to play around with this now that you have the technique?

Option Explicit

Dim varOldValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Count > 1 Then
        Exit Sub
    End If
    
    ' If a cell in column 2 is selected then the value is saved in the varOldValue variable.
    If Target.Column = 2 And Target.Offset(0, -1) <> "" Then
        varOldValue = Target.Value
    End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFound As Range

    ' Abort if the cell changed is not in column 2
    ' or the value in column 1 of the same row is blank.
    If (Target.Column <> 2) Or _
        (Target.Column = 2 And Target.Offset(0, -1) = "") Then
        Exit Sub
    End If

    ' Find consignment in column A on Sheet2
    Set rngFound = Worksheets("Sheet2").Range("A:A").Find(Target.Offset(0, -1), LookIn:=xlValues)
                        
    ' Abort if consignment is not found.
    If rngFound Is Nothing Then
        Exit Sub
    End If

    ' This is where you put the code that changes the values in Sheet2.
    ' Add in ElseIf lines and the corresponding following line as appropriate.
    
    ' rngFound holds the cell address in column A of Sheet2 for the consignment
    ' being edited in Sheet1
    
    'Target.value is the new value for the cell being edited.
    
    '.offset specifies which column in Sheet2 to change.
    ' The second argument specifies how many columns across from column A to change.
    
    If varOldValue = "Out" And Target.Value = "Not Out" Then
        
        rngFound.Offset(0, 1).Value = "Not Out"
        
    ElseIf varOldValue = "Not Out" And Target.Value = "Out" Then
                
        rngFound.Offset(0, 1).Value = "Out"
                
    ElseIf varOldValue = "" And Target.Value = "Out" Then
                
        rngFound.Offset(0, 1).Value = "Out"
                
    ElseIf varOldValue = "" And Target.Value = "Not Out" Then
                
        rngFound.Offset(0, 1).Value = "Not Out"
                
    End If
    
End Sub