Update cell based on value in another cell

Re: Update cell based on value in another cell

Can you post a sample worksheet that exhibits the issue. Including the macro.

Re: Update cell based on value in another cell

First thing you need to understand are how events work.

Your sub is called every time a change event happens on the sheet, your code then makes a change to a cell, which then calls the change event code, which then changes a cell, which then calls the change event code, which then changes a cell, which then calls the change event code, which then changes a cell......... (see where I'm going with this?)

So that's why Excel stops responding and restarts, it's stuck in an infinite loop of change triggers code, which makes change, which triggers code...

To stop this from happening you need to temporarily disable events in your code by using

Application.EnableEvents = False

Important: remember to turn them on again, good time to introduce some error handling.

The second part of your question is where you need to work with something like Intersect() - this method gets the intersection of two specified ranges, which we can use to see if the cell that caused the change event to fire is in column L

If Not Intersect(Target, Range("L:L")) Is Nothing Then
    '// cell is in column L, do something....
End If

Put all that together and you get something like:

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo CleanExit:


    If Not Intersect(Target, Range("L:L")) Is Nothing Then
         
        Application.EnableEvents = False
    
        If Target.Offset(0, -3).Value > 0 Then
            Target.Value = "Overdue"
        End If


        Application.EnableEvents = True


    End If


    Exit Sub


    CleanExit:
        Application.EnableEvents = True
        Err.Clear




End Sub

Re: Update cell based on value in another cell

Correct me if I'm mistaken, but isn't it column I the OP's wanting to fire the event?
Which won't fire Worksheet_change due to being a formula.

Re: Update cell based on value in another cell

Quote from NoSparks;785344

Correct me if I'm mistaken, but isn't it column I the OP's wanting to fire the event?
Which won't fire Worksheet_change due to being a formula.

Aha, yes you are correct - it's col I that's required as the target, and yes it wouldn't fire the change event but:

Quote

I was able to make this happen using the following code for one cell.

The fact that OP has it working means there is something more going on that is triggering the event that we don't know about, which I'm happy to roll with.

revised code:

Private Sub Worksheet_Change(ByVal Target As Range) 
     
On Error Goto CleanExit: 
     
    If Not Intersect(Target, Range("I:I")) Is Nothing Then 
         
        Application.EnableEvents = False 
         
        If Target.Value > 0 Then 
            Target.Offset(0, 3).Value = "Overdue" 
        End If 
         
        Application.EnableEvents = True 
         
    End If 
     
    Exit Sub 
     
CleanExit: 
    Application.EnableEvents = True 
    Err.Clear 
     
     
End Sub

Re: Update cell based on value in another cell

Hi josh

Have a look and see if this will work.

I changed the formulas down column "I" to do away with all those days over due in the yet to be used rows.

=IF(OR(ISBLANK(F11),ISBLANK(G11),ISBLANK(H11)),"",IF(TODAY()>H11,TODAY()-H11,""))

Because column I is formulas, need to use the Worksheet_Calculate event.
I've used column Z to hold a formula in Z7

=SUMPRODUCT(--(I9:I219<>Z9:Z219))

and a copy of I9:I219 in Z9:Z219.
If this location is a problem it could be made to use a separate sheet if need be.

If Z7 is 0, the two ranges match so it wasn't an I column calculation that triggered the event.
If Z7 isn't zero some value in column I is different so the macro proceeds.

Private Sub Worksheet_Calculate()


If [Z7] = 0 Then Exit Sub


    Dim CurCel As String
    Dim cel As Range
    
On Error GoTo CleanExit:


'for relocating cursor after the copy
CurCel = Replace(ActiveCell.Address, "$", "")


Application.ScreenUpdating = False
Application.EnableEvents = False


Range("I9:I219").Copy
Range("Z9:Z219").PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
Application.CutCopyMode = False


For Each cel In Range("I9:I219")
    If cel.Value <> "" Then
        cel.Offset(0, 3).Value = "Overdue"
    End If
Next cel


Range(CurCel).Activate


Application.ScreenUpdating = True
Application.EnableEvents = True


Exit Sub
     
CleanExit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Err.Clear


End Sub

Is changing L11 to 'Complete' (or anything else) after being automatically changed to 'Overdue' something you would normally do?
If so we'll need to address that too.

Re: Update cell based on value in another cell

Always test on a copy of the original and make sure you have a backup.

When you change L11 from Overdue to something else you'll need to blank I11 to prevent L11 changing back to Overdue.
This could be done with Worksheet_Change similar to SO's example in post #4 to remove the formula and blank I11.

Good luck with your project
NoSparks

Re: Update cell based on value in another cell

If you're happy, I'm happy.

It just didn't make sense to me for items that aren't even started to show as Overdue.

Don't know if you noticed the one line macro in the sheet module I used to change the formulas.
When I entered the formula and dragged it down the colors went goofy so I used the macro to change them all in one go.