Time Sheet Macro

[Akash]

Hi,

me try to follow a rule for all the employees of in my concern.

e.g., Working Hrs for all the employee is 9:30hrs to 18:45hrs. If any
one works till 20:30hrs in a day he or she can resume his next day's
duty by 10:30hrs in the mornining. But if he or she has work less than
20:30 hrs then he has resume his duty from 9:30hrs in the morning. To
ascertain this i created an excel sheets.

All the Columns (C1,E1,G1,I1,K1,M1,O1,P1....) has got time In
All the Columns (D1,F1,H1,J1,L1,N1,P1,Q1....) has got time out.

Now i want that if D1 is greater than or equal to 20:30 hrs it should
automatically becomes Bold otherwise it should be in normal fonts
(without Bold). Moreover if D1 greater than 20:30hrs then E1 can have
data in normal font only till 10:30hrs. As soon as the user enters
data greater than 10:30hrs in D1 it should automatically change the
font color in Red with strikethrough.

This would continue in all the cell (D1,F1,H1,J1,L1,N1,P1,Q1....)

How can this be possible.

I dont want to run macro all the time. It should work autometically in
the back end.

Awaiting for your reply.

Thanks

Akash

 

[lar.the.ever.so.slightly.lost]

Hi,

me try to follow a rule for all the employees of in my concern.

e.g., Working Hrs for all the employee is 9:30hrs to 18:45hrs. If any
one works till 20:30hrs in a day he or she can resume his next day's
duty by 10:30hrs in the mornining. But if he or she has work less than
20:30 hrs then he has resume his duty from 9:30hrs in the morning. To
ascertain this i created an excel sheets.

All the Columns (C1,E1,G1,I1,K1,M1,O1,P1....) has got time In
All the Columns (D1,F1,H1,J1,L1,N1,P1,Q1....) has got time out.

Now i want that if D1 is greater than or equal to 20:30 hrs it should
automatically becomes Bold otherwise it should be in normal fonts
(without Bold). Moreover if D1 greater than 20:30hrs then E1 can have
data in normal font only till 10:30hrs. As soon as the user enters
data greater than 10:30hrs in D1 it should automatically change the
font color in Red with strikethrough.

This would continue in all the cell (D1,F1,H1,J1,L1,N1,P1,Q1....)

How can this be possible.

I dont want to run macro all the time. It should work autometically in
the back end.

Awaiting for your reply.

Thanks

Akash

try playing with worksheet_change in the worksheet module. as the
value in the intercept changes, the worksheet automatically excecutes
the code. the following code will work for the data in columns, not in
rows, as you currently have them.

maybe something like this (you'll need to double check it, but here's
the general idea):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim target_address As String


If Intersect(Target, Range("D1100")) Is Nothing Then
Exit Sub
Else
'check to make sure the target (hours
inputed) is numeric and greater than 20:30
'you may need to enter the hours as 20.5
instead of 20:30 to calculate things. I'm just using 20.5 in the
example

If (IsNumeric(Target) = True) And (Target >
20.5) Then
'make the number bold
Target.select
Selection.Font.Bold = True
end if

'set E1 to normal only after 10.5 hours
'if E1 has data greater than 10.5 hours,
make red.
Range("E1").select
if (selection >10.5)
'make red

Selection.Font.ColorIndex = 3
end if

end if


hope that helps get you started in the right direction

 

[Ed]

Take a look at
http://www.j-walk.com/ss/excel/files/timesht.htm

Ed

 

[JNW]

I would just use conditional formatting. No need to use macros for this.