new number should be greater then previous number

[Mandeep Dhami]

Hi,

Please could you help me write formula for entering a six digit number in a
column where the new number entered is greater then the previous number.
If I try to enter a number lesser then the previous number the same should
prompt and should not accept the same. The six digit numbers will not be in a
serial order but the every next number will be grater then the previous
number. If in error we try to enter the lesser digit number the same should
prompt.

Example:

359311
359324
359325
359320 (this number should not be accepted)

Cheers,
Mandeep

 

[Bernard Rey]

Mandeep Dhami:

Please could you help me write formula for entering a six digit number in a
column where the new number entered is greater then the previous number.
If I try to enter a number lesser then the previous number the same should
prompt and should not accept the same. The six digit numbers will not be in a
serial order but the every next number will be grater then the previous
number. If in error we try to enter the lesser digit number the same should
prompt.

You may find a way using the "Conditional Formatting":
- Select cell A2.

- From the "Format menu" pick the "Conditional formatting..." item.

- Leave the first drop-down menu on "Cell Value Is"

- From the second drop-down menu, pick the "Less than" line.

- In the remaining field type "=A1" (without the quotes)

- Click on the "Format..." button and select, let's say a red cell shading
color (or a red font color, or anything else). Click the "OK" button.

-click the "OK" button and copy the cell format down.

Now, when you enter a number that is lesser than the one in the cell above,
the background will display in red (or any other setting you chose).

Will that be a sufficient warning? If not, something can be done with an
event macro, placed in the sheet's Code sheet (you can display it with a
Control-click on the sheet tab). You can try this one:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set isect = Application.Intersect(Target, Range("A:A"))
If isect Is Nothing Then
Exit Sub
Else
MyRow = Target.Row
If Target.Value < Range("A" & MyRow - 1).Value Then
Target.Value = "not " & Target.Value & "!"
temp = MsgBox("You can't enter that value!")
End If
End If
End Sub

 

[Mandeep Dhami]

Thanks a lot Bernard for the reply.
I did not understand how to write the macro, but the other formula was of a
little help.
I want that the cell should not accept the number lower then the previous
number.

Cheers,
Mandeep

 

[JE McGimpsey]

Please could you help me write formula for entering a six digit number in a
column where the new number entered is greater then the previous number.

One way:

Say your range is A1:A1000. Select all but the first cell of your range,
i.e., A2:A1000...

Choose Data/Validation. Set the dropdowns and textboxes to read

Allow: Custom
Formula: =AND(A2>=100000,A2<=999999,A2>A1)

Set the alert and error messages to suit.