Need to delete all text in cells with time format in them??

[daniels012]

I have a timesheet that I protect. I would like to write code that will:
Unprotect the sheet then...
delete all the times in cells that have borders.

Is this even possible?

Michael

 

[daniels012]

Can I do something to this code for borders??

Sub ClearYellowCells()
Dim cl As Range
Sheets("Worksheet").Unprotect
For Each cl In ActiveSheet.UsedRange
If cl.Interior.ColorIndex = 6 Then cl.MergeArea.ClearContents
Next cl
Sheets("Worksheet").Protect
End Sub

Michael

 

[daniels012]

tried jindon's code with no success.
I need it to do it to the active sheet. Can I add the code to the active sheet instead of Sheet 1

 

[daniels012]

I use this code to add a new timesheet:

Sub AddSheetNew()
'
ActiveSheet.Unprotect
ActiveSheet.Shapes("Button 4").Visible = False
ActiveSheet.Select
ActiveSheet.Copy after:=Worksheets(Worksheets.Count - 1)
Range("C4") = Range("R2")
ActiveSheet.Name = Range("R1").Value
ActiveSheet.Shapes("Button 4").Visible = True
Range("TimeArea").Select
    Selection.ClearContents
Range("D6").Select
End Sub

Can I add something to clear any cell, that has a time in that cell?

Michael

 

[daniels012]

bump

 

[jindon]

Hi
Daniel
Did I post any code here?
How about

Sub AddSheetNew()
Dim r As Range
With ActiveSheet
   .Unprotect
   .Shapes("Button 4").Visible = False
   .Copy after:=Worksheets(Worksheets.Count - 1)
End With
With ActiveSheet
   .Range("C4") = .Range("R2")
   .Name = .Range("R1").Value
   .Shapes("Button 4").Visible = True
   .Range("TimeArea").ClearContents
   For Each r In .UsedRange
      With CreateObject("VBScript.RegExp")
         .Pattern = "^\d{1,2}:\d{1,2}(:\d{1,2})?$"
         If IsDate(r) And .test(r.Text) Then
            If Int(r.Borders(xlEdgeTop).LineStyle = xlNone) + _
               Int(r.Borders(xlEdgeBottom).LineStyle = xlNone) + _
               Int(r.Borders(xlEdgeLeft).LineStyle = xlNone) + _
               Int(r.Borders(xlEdgeRight).LineStyle = xlNone) > -4 Then
               r.CelarContents
            End If
         End If
      End With
   Next
End With
End Sub

 

[daniels012]

jindon,
Thank you for responding!!!!
My times are not being erased. Should I change IsDate to IsTime?

Michael

 

[daniels012]

All of my times are with in the range
C6:I190

There are formulas with totals included in this range also this is why I want to erase the times not the formulas.

I will post a sample maybe.

Michael

 

[daniels012]

Here is a sample:

TimesheetShopComputerized.xls
	A	B	C	D	E	F	G	H	I
163	Antonio	TimeIN		7:45AM
164		TimeOUT		8:30AM
165		TimeIN
166		TimeOUT
167		TimeIN
168		TimeOUT
169		TimeIN
170	Total	TimeOUT
171			0:00	0:45	0:00	0:00	0:00	0:00	0:00
172	0.75		0:00	0:45	0:00	0:00	0:00	0:00	0:00
173	Jesus	TimeIN		7:45AM
174		TimeOUT		8:30AM
175		TimeIN
176		TimeOUT
177		TimeIN
178		TimeOUT
179		TimeIN
180	Total	TimeOUT
181			0:00	0:45	0:00	0:00	0:00	0:00	0:00
182	0.75		0:00	0:45	0:00	0:00	0:00	0:00	0:00
2006 10-1 to 10-7

Thank You,
Michael

 

[daniels012]

bump

 

[jindon]

That is why I asked how your cells are formatted...
How about this one?

Sub AddSheetNew()
Dim r As Range
With ActiveSheet
   .Unprotect
   .Shapes("Button 4").Visible = False
   .Copy after:=Worksheets(Worksheets.Count - 1)
End With
With ActiveSheet
   .Range("C4") = .Range("R2")
   .Name = .Range("R1").Value
   .Shapes("Button 4").Visible = True
   .Range("TimeArea").ClearContents
   For Each r In .UsedRange
      With CreateObject("VBScript.RegExp")
         .Pattern = "^\d{1,2}:\d{1,2}\s(AM|PM)$"
         If IsDate(r) And .test(r.Text) Then
            If Int(r.Borders(xlEdgeTop).LineStyle = xlNone) + _
               Int(r.Borders(xlEdgeBottom).LineStyle = xlNone) + _
               Int(r.Borders(xlEdgeLeft).LineStyle = xlNone) + _
               Int(r.Borders(xlEdgeRight).LineStyle = xlNone) > -4 Then
               r.CelarContents
            End If
         End If
      End With
   Next
End With
End Sub