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

 

[JackDanIce]

This link may help: Custom Format in Excel – How to Format Numbers and Text | Excel & VBA – Databison

 

[l1011driver]

Hi JackDanIce,

Thanks for this link, but to be honest I've seen this one and several others regarding number format and they were of little help. I have three problems:

1. I don't really know the syntax for the logic to account for the difference in output between the two input values
2. I can't find anything to resolve Excel returning "30" instead of "30.00" when I input 3000. I tried setting up a custom format "##.##" and it still returned "30".
3. I don't know the syntax for an error message when the input values are outside of the ranges that I describe.

Any advice would be appreciated. Thanks for taking time to reply.

 

[JackDanIce]

Reading your post again and your reply, I don't think you can achieve what you want just with cell formatting.

Worksheet event code would suit your purpose better - say your range for input is A1:A10, then putting this into the worksheet object will achieve what you need:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim blerr As Boolean
    
    If LenB(Target.value) = 0 Then Exit Sub
    
    If Not Intersect(Target, Range("A1:A10")) Is Nothing And Target.Cells.count = 1 Then
                
        If Target.value >= 2500 And Target.value <= 3500 Then
            Application.EnableEvents = False
            Target.value = Target.value * 0.01
            Application.EnableEvents = True
            Exit Sub
        End If
        
        If Target.value < 700 Then blerr = True
        If Target.value > 3500 Then blerr = True
        If Target.value > 1500 And Target.value < 2500 Then blerr = True
        
        If blerr Then MsgBox "Invalid entry, try again"
        
    End If

End Sub

 

[smallxyz]

For 3), you may consider applying Data Validation.

I think there is no way to format 3000 as 30.00 using custom format and I bet it is impossible to have an instant result after inputting without relying on VBA or other clicking-buttons.

 

[JackDanIce]

@smallxyz see Custom Number Formats (Multiply & Divide by any Power of 10) | Chandoo.org - Learn Microsoft Excel Online

 

[smallxyz]

@smallxyz see Custom Number Formats (Multiply & Divide by any Power of 10) | Chandoo.org - Learn Microsoft Excel Online

Cool! Thank you JackDanIce.

 

[l1011driver]

Dear JackDanIce,

I agree with you about the cell formatting. I should have been more clear. I think it will take a macro as well.

I copied and pasted your code in "This Workbook". I changed the cell range to the range appropriate to my spreadsheet.

The result was, no matter which number I typed in (within the valid range). Excel returned that value with a decimal point behind it. For example, I typed in 3001 and it returned "3001.". I typed in 1015 and it returned "1015.".

I did not try any numbers outside the valid range to test the error message.

Any thoughts?

Thank you for your kind and patient help.

Mark

 

[JackDanIce]

I got the results you were expecting, e.g. I typed in 2600 and it displays as 26, I then tried 2725 and cell shows 27.25.

The code needs to go into the sheet object, NOT ThisWorkbook, specifically the sheet object (below ThisWorkbook) that you want it to action on.

 

[l1011driver]

Oh boy...I'm gonna reveal the true level of my ignorance here...

Sheet object below ThisWorkbook? You mean a module? I see a bunch of sheet objects above ThisWorkbook that correspond to the name of each worksheet. I did click on the sheet object with the name of the sheet and pasted the code in there, but nothing happened.

Also, if you type in a number that's divisible by 100, like you typed in 26, I want it to return 26.00 not just "26".

If it helps, the cell is formatted as "General".

Grateful for your continued patience.

Mark