Detecting a cell value change

[Peter_Field]

Hi, all. I have a simplified section of a complex (?!) spreadsheet I'm struggling with.

Entering a destination and journey finish mileometer reading fills a cell with the mileage.

For payment, this mileage is segregated into bands, eg 1 to 100 miles, Band 1, 101 to 200 miles, Band 2.

For one worksheet, this should be relatively easy, even for me! But I just can't get it to work.

When the "mileage" cell value changes, it should call a sub which fills in the "banding" cell relative to the mileage value.

But, and this is the first problem, entering the finish mileage, which allows the "mileage" cell to display, triggers the change mechanism, steering the Target_Address away from the intended target ("mileage") to itself, "Finish Miles". 

The second problem may be solved if the first one is, perhaps. How to have the sub sitting in the module, being called by the Active Sheet, instead of sitting in each sheet. There are many more sheets than in the excerpt.

Any and all help / advice will be appreciated.

Best regards, Pete

[#2 · Accepted solution]

@Peter_Field Don't think you need VBA here. You can use a lookup table like in the attached workbook.

[#3]

@Peter_Field 

Do you really need code? You could enter the following formula in E2, then fill down as far as you want:

=IF(D2="","","Band "&ROUNDUP(D2,-2)/100)

[#4]

Thank you, Hans. I will try this.
I haven't come across "Roundup" before, so will search to learn more.
Best regards, Pete

[#5]

Hi, friend
Thank you so much for your response. It took me totally by surprise, as I never considered using Lookup in this aspect.
Many thanks, again, for this neat solution.
Best regards, Pete