I am working with a spreadsheet that contains whole hour times for Regular,
Over, Sick and Vacation times. When 9 is placed in Regular time, I want the
number to remain at the max number of 8 hours Regular time, moving the 1 hour
Over time into the next cell.


Reg Over Sick Vacation
Monday 9 1 0 0
Tuesday 8


When I type that 9 in, I am looking for a function or VBA code that can
automatically make it 8 and move the 1 over. I have figured out how to move
the 1 over using =IF(C88,C8-8,"-") but it would be nice to be able to type
that 9 and it will automatically make it 8 and then a 1 goes into the Over
column.

Any suggestions? Thanks!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value 8 Then
.Offset(0, 1).Value = .Value - 8
.Value = 8
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Marhanen" wrote in message
...
I am working with a spreadsheet that contains whole hour times for
Regular,
Over, Sick and Vacation times. When 9 is placed in Regular time, I want
the
number to remain at the max number of 8 hours Regular time, moving the 1
hour
Over time into the next cell.


Reg Over Sick Vacation
Monday 9 1 0 0
Tuesday 8


When I type that 9 in, I am looking for a function or VBA code that can
automatically make it 8 and move the 1 over. I have figured out how to
move
the 1 over using =IF(C88,C8-8,"-") but it would be nice to be able to
type
that 9 and it will automatically make it 8 and then a 1 goes into the Over
column.

Any suggestions? Thanks!

Try this. It may not be the most effecient use of system resources but
it works.

Unfortunately, it fires on EVERY cell that has a number greater than 8,
not just the regular hours.


Code:
--------------------
Public oldcell As String
---------------------
Private Sub Worksheet_Activate()
oldcell = ActiveCell.Address
End Sub
---------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Range(oldcell).Value 8 Then

Range(oldcell).Select
temphrs = ActiveCell - 8
ActiveCell.Value = 8
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + temphrs
End If
oldcell = ActiveCell.Address


End Sub
---------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldcell = ActiveCell.Address

End Sub
--------------------


--
widemonk


------------------------------------------------------------------------
widemonk's Profile: http://www.excelforum.com/member.php...o&userid=20402
View this thread: http://www.excelforum.com/showthread...hreadid=487644