Unintended effect VBA code for drag and drop

AramA92 

"If the user would drag and drop any of the cells they are allowed to edit, they will brick the references of the formulas"

That is a 'feature' inherent in the way in which spreadsheets are conceived and built (or, at least, an unintentional consequence)!  With traditionally-built spreadsheets, developers frequently have cause to shake their heads over the manner in which 'stupid' users butcher the spreadsheet by failing to appreciate that 'drag and drop' is a capital offence!

I prefer to accept that drag and drop can be a very efficient way of correcting data input errors, and try to write formulas to accept such steps.  I approach this by writing Excel 365 array formulas (and using structured references) for all data processing steps.  These are far more robust than the traditional single-cell relative referencing when it comes to moving the furniture about.

Doesn't help with the data validation or conditional formatting, though.  Those are ancient pieces of code that are no longer fit for purpose, but it will take a massive effort to bring them into the 21st century.