Format number based on input value

[l1011driver]

Thank you in advance for any help. I'm a novice so no help is too remedial for me.

I have a worksheet in which I'd like to format a cell such that the number entered is formatted based on the input value.

If the entered value is between 2500 and 3500 I would like for Excel to return that number with a decimal inserted to two decimal places. For example, if I enter "3015" I'd like Excel to return the value "30.15". If I enter "3000" I'd like Excel to return the value "30.00" (as opposed to "30").

If the entered value is between 700 and 1500 I would like for Excel to return that number. For example, if I enter "1025" I'd like Excel to return the value "1025". If I enter "987" I'd like Excel to return the value "987".

The entered numbers will be only positive integers in these number ranges. Numbers outside these ranges would be rejected. This would apply to only one cell in one worksheet.

Thanks in advance for any help anyone might be.

Sincerely,

l1011driver

 

[Peter_SSs]

It seems to me that you are going to too much trouble using code to change cells to upper case.
Could you just use Excel's standard Data Validation to ensure entries are made in upper case in the first place?

With the sheet unprotected, for cell AF10, enter Data Validation -> Custom -> Formula: =EXACT(AF10,UPPER(AF10))

.. and add a message on the Error Alert tab if you want. Copy that Data Validation to the rest of that row 10 range and re-protect the sheet.

 

[l1011driver]

Hi Peter,

Thank you for your suggestion and I agree. However, several years ago I did the very first aircraft weight and balance program for these guys and did just what you suggest...a reminder to just type in all caps. It was always a little sticking point for them and it's something I promised them I would fix. They're pilots, and pilots can sometimes be perfectionists. If it were left up to me what you're saying is exactly what I would do (and what I would do if I were the user) but I just wanted to make good on this.

No kidding...thanks for all your help. I apologize for making this more difficult than it should have been. I wish I had 10% of what you and Jack know about this. I can see how programming could get addictive.

Sincerely,

Mark

 

[JackDanIce]

Glad it's resolved Mark,
J