Optimise Multiple Worksheet_Change(ByVal Target As Range) - worksheet slows down as more rows added

  • I have two functions that operate 'live' on a worksheet:

    1, Fill cell ("A",i) with a pre- determined colour based on the number keyed in at that location.

    2. For a date that is keyed in at ("L",x), do one of three things:

    If the date is less than two years from todays date, colour the cell next to it green and insert text "OK"

    If the date is more than two years from todays date, colour the cell next to red and insert text "Overdue"

    The idea is that as dates are added to rows in column L, it is compared to today's date and as the date approaches a two year anniversary, it will flag up as yellow and being due, after that, if the date isn't updated, and goes over a two year point then will flag up as red and being overdue.

    This seems to work fine sometimes but as I insert dates further down the rows, the blue spinning circle starts to appear and takes longer and longer to think about updating.

    Clearing a cell doesn't always seem to clear the cell next to it either.

    Could someone take a look at my code and offer words of wisdom to optimise it?

    Additionally I've been trying to add an extra condition to function 2 that does this:

    2. If the date in ("L",x) is within two months of its two year anniversary and becoming overdue, colour the cell next to it yellow and insert text "Due"

    I can't seem to get to grips with the logic

    Many thanks


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!