Melissa,

The basic problem is that you need to convert 2300 into time and format as
such, but your code seems overly complex as well. Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo wb_exit
Target.Value = Target.Value / 24 / 100
Target.NumberFormat = "hh:mm"

wb_exit:
Application.EnableEvents = True

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"MelissaCreek " wrote in
message ...
Hi,
What I am trying to achieve is to be able to alter the contents of a
particular cell. If I type 2300 into the cell I want the cell to
automatically update with 23:00

The code I have so far is:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim TargetRow As String
Dim TargetColumn As String
Dim IntValue As String
Dim TargetHours As String
Dim TargetMins As String

TargetRow = Target.Row
TargetColumn = Target.Column

IntValue = Format(Target.Worksheet.Cells(Target.Row,
Target.Column).Value, "0000")

TargetHours = Left(IntValue, 2)
TargetMins = Right(IntValue, 2)

Target.Worksheet.Cells(Target.Row, Target.Column) = TargetHours & ":" &
TargetMins
End Sub

the trouble is that the cell gets updated with 00:00 all the time.
When I try to format the cell (after it has been formatted) it is
always on a custom format of hh:mm which is OK but I am thinking the
formatting may be at fault somewhere??

I am using office 2000.

Any help is appreciated.


---
Message posted from http://www.ExcelForum.com/