Reference to named range not working when sub is converted to a worksheet_change sub

  • When I run the following macro as a normal sub it works fine but when I convert it into a worksheet_change sub it gives me an error on the For Each line.

    Can somebody please explain why and show me how to fix it?


    The error message I received is:
    Run-time error '1004'
    Method' Range of object'_Worksheet failed



    The active cell has a drop-down list in it and I would like to colour the cell depending on the choice made. Unfortunately you can't pass through formatting when you use a drop-down list.
    My use of VBA is pretty much self-taught so I always have trouble figuring out the correct syntax of some of these.

  • That will be fine, just make sure the structure of the sample workbook is the same as the actual workbook (named ranges the same, any formulas still formulas, formatting the same etc.)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • In a worksheet code module, an unqualified Range call refers to the worksheet containing the code, so you'd get an error if that range is in another sheet. You should use Application.Range("Carer_initials") instead of just Range("Carer_initials")

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why