Using a Target.Value expression when the referenced cell value is generated from a formula

Good morning/afternoon/evening everyone! Something I ran into today that I've searched the depths of the web to find a solution to, with no luck, is that I have a formula in many rows of Column "I" of a worksheet. The formula in each cell within Column I looks at the values of the corresponding cell in Columns A, F, G, and H to determine a "Yes" or "No" value. If the formula generates a "Yes" then great-- I don't need to do anything. However, if the formula generates a "No", then I need to automatically change the value of the corresponding cell in Column "R" to "Disabled".

Here is the VBA that I started with:

Please Login or Register  to view this content.

The above code works perfectly if I manually enter "No" in Column I. I've used this same code for many years for data that is manually entered. But I've tried a slew of suggestions I've found across other people trying to accomplish this same thing as me today (using a formula), and can't find an answer that works for me. This is also just a small sample of data.... I have a few hundred rows of data to maintain, so this is why I rely on a formula in Column I.... manual text entry in Column I is really not an option.

Does anyone know how I can accomplish using a "No" in Column I that has been generated by a formula, to then generate the string "Disabled" in the corresponding cell in Column R? I have an example file here:

TestFile.xlsm

Thanks so much!!!!!!!!!!!!!!!!

Thanks for the assistance MrShorty! So unfortunately if I remove the "If Target.Column = 9" statement out, it would impact other columns that have a "No" as a result. I apologize that my sample worksheet I attached doesn't reflect that, I wasn't sure how much sample info I'd have to provide that was relevant to my actual worksheet, but I'll definitely do better on that next time

The only problem with using a formula in Column I is that this column has data validation on my actual worksheet, forcing the end-use to select from a drop-down list. So I want the end-user to be able to manually choose from that drop-down list as they manually change the status, because the other options in the list cannot be automatically decided by a formula. Basically, a "Yes" in Column I could be a "Pending", "Enabled", OR "Removed" status in Column R, but the end-user would have to manually adjust that status using their first-hand knowledge. I guess there's really not an easy way to decide if a user meets the requirements (based on columns F, G, H) but has to be Removed because the user is no longer employed here, for instance. The only definitive is if the user should be in Disabled status.

Hope that makes sense, and totally understand shying away from VBA. I was intimidated about for a while (not suggesting you are), but when I learned there were certain things I wanted to automate and formulas weren't always capable, I started dabbling with VBA and it's pretty enjoyable. I do formulas where I can, but VBA can really do some powerful stuff

Thanks so much for the assistance Rory, and my apologies to both you and MrShorty...... sounds as if I misunderstood how to implement what was being suggested. However, your code that you provided worked like an absolute charm Rory. I really appreciate the assistance from both of you!