Update cell based on value in another cell

  • This seems like it would be simple. I have a project plan with due dates for each task in a column. In column "L" I am using a data validation list to show the current Status, giving only the options of Complete, In Process, Overdue, or Not Started. In column "I", I have a formula to calculate if a task is overdue (basically today - due date for any incomplete tasks), and it gives the number of days overdue.


    I would like the Status to automatically update to "Overdue" in column L if the value in column I is > 0. I was able to make this happen using the following code for one cell. The issue is that if I then manually update cell L11, say if the task is now Complete, Excel throws an error, stops working, and restarts.


    So two questions: How do I update the code so that it doesn't break Excel when I update the status, and what changes do I need to make for this code to work on every cell in column L, not only cell L11.


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Range("I11").Value > 0 Then
    3. Range("L11").Value = "Overdue"
    4. End If
    5. End Sub



    First screen is showing that L11 updated to Overdue. Second screen is what happens if I manually select Complete from the drop-down in cell L11.
    [ATTACH=CONFIG]71323[/ATTACH][ATTACH=CONFIG]71324[/ATTACH]

  • 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


    Code
    1. 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


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


    Put all that together and you get something like:


  • 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:


  • Re: Update cell based on value in another cell


    This did not work. I apologize because I am a complete novice when it comes to VBA. I know what I want to do, but no clue how to tell you what you need to know. If it helps, I can create a sample Excel sheet on a smaller scale and send it to you.

  • Re: Update cell based on value in another cell


    ozgrid.com/forum/core/index.php?attachment/71326/


    Okay, I'm attaching a sample workbook with a lot of the data taken out. But this functionality would still work. So Column L is a data validation list. If the Due Date in H11 is before today (calculated in I11) and there is no Completion Date in G11, it will cause cell L11 to update to "Overdue" with some conditional formatting to turn it red. Then if I go to change L11 to Complete, the error begins.

  • 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.

    Code
    1. =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

    Code
    1. =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.


    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


    Wow, thank you NoSparks. The copy you sent back works like I need it to. Even you last question about changing L11 to Complete works just fine. I am able tochange from Overdue to anything else without throwing an error. I'm going to copy all of this code and your column Z formulas back into my original workbook and will let you know if it works.

  • 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


    I didn't make the change that you mentioned below because I don't mind those numbers being there. So the cells in column I do turn blank once there is a completion date noted. Thus, it is working as expected, even when I make a change to the status.


    "I changed the formulas down column "I" to do away with all those days over due in the yet to be used rows.
    VB:
    =If(Or(ISBLANK(F11),ISBLANK(G11),ISBLANK(H11)),"",If(TODAY()>H11,TODAY()-H11,""))
    "

  • 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.