First, 100% = 1. So you only need to check to see if the value is 1.

But entering percentages in excel can be maddening. There's an option under
tools|options|edit tab that can make life easier (or worse--depending if you
like it or not).

From xl2003's help:

Enable automatic percent entry Select to multiply by 100 all numbers less than 1
that you enter in cells formatted in the Percentage format. Clear this check box
to multiply by 100 all numbers that you enter in cells formatted in the
Percentage format, including numbers equal to or greater than 1.

(I'd format that whole column as a percentage first.)

And your code showed column 3 (C) instead of column 5 (E) in your text. I used
E.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("b:b")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 3)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

You can uncomment the .style command if you didn't format it as percentage.

The .enableevents stuff stops the code from getting called again when you add
the date/time.




Mikus wrote:

I wrote following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 And Target.Value = 100 Then
Cells(Target.Row, 3).Value = Date & " - " & Time
Target.Value = Target.Value / 100
Target.Style = "Percent"
End If

End Sub

The idea of this is when i enter value 100 in column B, then timestamp is
inserted in column C.
I know this code sux - last 2 rows of this code were written cuz i didn't
know how to triger this macro by entering value - 100%.
I want to know:

1) how do i triger a macro when value 100% is entered? I mean 100 is entered
in cell which is formated as % ?
2) how do i format my timestamp so it enters date & time and then i can add
rest of the formating through cell formating ? For example when i enter date
and time by pressing ctrl + ; then space and then ctrl + shift + ; i get
"date time" and after this i can format this datetime as i want. But after i
get the same through macro excel does not respond to formating.
3) i want to add last modified timestamp in column 5 which would trigger
each time value is entered/changed in column 2 how do i do this ?

--

Dave Peterson