ByVal function and R/Time Error 13

[Alexander.S]

Hello everyone,

I am new to this forum and sorry if my question is already answered in different thread. I was trying to make a semi-automated time accounting macro. The idea is that this macro allows user to simple click on a specific sell which will automatically displays current time (start time) and after the work is finished user shall click on the next cell which also displays current time and one of the sell makes a calculation for time between start and end of the work.
This is the macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 13 Or Target.Column < 4 Or Target.Cells.Count > 1 Or Target.Cells.Value > 0 Then Exit Sub
With Target
.Value = Now
.NumberFormat = "HH:MM"
With Cells(.Row, 14)
.Value = Cells(.Row, 5) - Cells(.Row, 4) + _
Cells(.Row, 7) - Cells(.Row, 6) + _
Cells(.Row, 9) - Cells(.Row, 8) + _
Cells(.Row, 11) - Cells(.Row, 10) + _
Cells(.Row, 13) - Cells(.Row, 12)
.NumberFormat = "[HH]:mm:ss"
End With
End With
End Sub
_________________________________________________________________________________
The problem I experience and required your advise is the following:

Whenever I select more than one cell, excel pops up “Run-Time Error 13 – Type mismatch”. In debugging screen it highlights “If Target.Column > 13 Or Target.Column < 4 Or Target.Cells.Count > 1 Or Target.Cells.Value > 0 Then Exit Sub”

Since I am not experienced in macros I cannot understand what is wrong with above script. However, when I delete “Or Target.Cells.Value > 0” from above sentence error 13 never pops up again, but:
1. I need the restriction “Target.Cells.Value > 0” to avoid macro inserting current time in cells that are already filled;
2. When “Or Target.Cells.Value > 0” is removed and I use Ctrl+A command it pops up a “Run-Time error 6 - Overflow”.

Could you please advise me what to do? If it is impossible to fix the macro, can I include in the macro restriction to prevent error pop-up?

Thank you very much for your help and support

[JosephP]

target.value is an array if you select more than one cell and you cannot compare an array with a value in that way
try two lines

Please Login or Register  to view this content.

[Alexander.S]

Thank you very much Joseph, your suggestion is 100% correct

So easy and so true...

[JosephP]

you're welcome :-)