Need Help with multiple Sheets

J

JoeSalmi

Board Regular
Joined
Jan 31, 2011
Messages
128
Can someone give me a hand in converting this code to work.

Code: 
            ' USES B. MILIC'S PAGE AS REFERENCE
    'Selects start and stop times along with job hours and clears them
    Sheets(Array(Sheet2, Sheet3, Sheet11, Sheet12, Sheet14, Sheet15, Sheet16, Sheet17, _
        Sheet18, Sheet19, Sheet20, Sheet21)).Select
	Sheets("Sheet2").Activate
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With

All of those sheets are the same layout but what I am trying to do is clear all the data from "C8:I10,C14:I41" and then replace the line colors from A14:J40 with having the colors alternate on each row.

I know I can could physically change row colors by using =MOD but the problem with that is I need to change the colors depending on what each employee is doing on that current week.

Also if I can I would like to get it so it doesn't flip through all the worksheets but kind of clears and recolors behind the scenes.

Thanks in advance.
Joe
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Maybe something like this? Mike

Code: 
Private Sub Workbook_Activate()
Call Color
End Sub

Code: 
Sub Color()
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets
   wks.Activate
 
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
 
 
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
        Call Tester1
         Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
        Call Tester2
 
 Next wks
 
 Application.ScreenUpdating = True
 
End Sub
Sub Tester1() 'Even
Dim TheRg As Range
Dim oRow As Range
'// Select the range to change
    Set TheRg = Selection
    For Each oRow In TheRg.Rows
        If (oRow.Row / 2) = Int(oRow.Row / 2) Then
            With Rows(oRow.Row).Interior
                .ColorIndex = 3
                .Pattern = xlSolid
            End With
        End If
    Next
End Sub
Sub Tester2() 'odd
Dim TheRg As Range
Dim oRow As Range
'// Select the range to change
    Set TheRg = Selection
    For Each oRow In TheRg.Rows
        If (oRow.Row / 1) = Int(oRow.Row / 1) Then
            With Rows(oRow.Row).Interior
                .ColorIndex = 5
                .Pattern = xlSolid
            End With
        End If
    Next
End Sub
 
Upvote 0
I think the problem with that might be that I have about 8 other worksheets in the SS that I don't need to change so that's why I have the WS's listed out. Will your change affect "all" the worksheets or just the 12 that I need it to?
 
Upvote 0
JoeSalmi said:
I think the problem with that might be that I have about 8 other worksheets in the SS that I don't need to change so that's why I have the WS's listed out. Will your change affect "all" the worksheets or just the 12 that I need it to?
Click to expand...

Maybe this change would not effect your other sheets.

Code: 
Sub Color()
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets
   wks.Activate
   
    If wks.Name = "Sheet2" _
    Or wks.Name = "Sheet3" _
    Or wks.Name = "Sheet11" _
    Or wks.Name = "Sheet12" _
    Or wks.Name >= "Sheet14" And wks.Name <= "Sheet21" Then
     
    
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
   
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
        Call Tester1
         Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
        Call Tester2
    End If
 Next wks
 
 Application.ScreenUpdating = True
 
End Sub
Sub Tester1() 'Even
Dim TheRg As Range
Dim oRow As Range
'// Select the range to change
    Set TheRg = Selection
    For Each oRow In TheRg.Rows
        If (oRow.Row / 2) = Int(oRow.Row / 2) Then
            With Rows(oRow.Row).Interior
                .ColorIndex = 3
                .Pattern = xlSolid
            End With
        End If
    Next
End Sub
Sub Tester2() 'odd
Dim TheRg As Range
Dim oRow As Range
'// Select the range to change
    Set TheRg = Selection
    For Each oRow In TheRg.Rows
        If (oRow.Row / 1) = Int(oRow.Row / 1) Then
            With Rows(oRow.Row).Interior
                .ColorIndex = 5
                .Pattern = xlSolid
            End With
        End If
    Next
End Sub
 
Upvote 0
That doesn't error out but it isn't clearing the cells or changing the worksheet cell colors either.
 
Upvote 0
Here is all the code and what I did to make it work. Not the smartest way I'm sure but it's a temp fix for what I need to do.

If anyone can simplify it that would be awesome.

Code: 
Sub TS_New_Week()
Dim Answer_nw As String
Dim MyNote_nw As String

    'Message goes here
    MyNote_nw = "Are you sure that all the information in the current week is correct?"
    

    'Display MessageBox
    Answer_nw = MsgBox(MyNote_nw, vbQuestion + vbYesNo, "New Week Confirmation")

    If Answer_nw = vbNo Then
        'Code for No button Press
        Close
    Else
        'Code for Yes button Press
		
	Application.ScreenUpdating = False
        
            'OPENS DTP PAGE
    'This moves the "This weeks P/D Owed" over to "P/D Earned TEMP".
    Sheet5.Select
    Range("F4:F15").Select
    Selection.Copy
    Range("L4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'This moves "Total Hrs & Total OT Hrs" over to "Temp Hrs & Temp OT Hrs".
    Range("D4:E15").Select
    Selection.Copy
    Range("J4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'This copies "Total (job) Hrs" over to "Temp (job) Hrs".
    Range("E19:E206").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
            ' USES B. MILIC'S PAGE AS REFERENCE
    'Selects start and stop times along with job hours and clears them
	Sheet2.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
	           ' SHEET3
    'Selects start and stop times along with job hours and clears them
	Sheet3.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET11
    'Selects start and stop times along with job hours and clears them
	Sheet11.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET12
    'Selects start and stop times along with job hours and clears them
	Sheet12.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET14
    'Selects start and stop times along with job hours and clears them
	Sheet14.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET15
    'Selects start and stop times along with job hours and clears them
	Sheet15.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET16
    'Selects start and stop times along with job hours and clears them
	Sheet16.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET17
    'Selects start and stop times along with job hours and clears them
	Sheet17.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET18
    'Selects start and stop times along with job hours and clears them
	Sheet18.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET19
    'Selects start and stop times along with job hours and clears them
	Sheet19.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
		           ' SHEET20
    'Selects start and stop times along with job hours and clears them
	Sheet20.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	
			           ' SHEET21
    'Selects start and stop times along with job hours and clears them
	Sheet21.Select
    Range("C8:I10,C14:I41").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
	'INSERT/REINSERTS ORIGINAL COLORS
	'Selects all even rows and paints them a light blue
        Range( _
        "A14:J14,A16:J16,A18:J18,A20:J20,A22:J22,A24:J24,A26:J26,A28:J28,A30:J30,A32:J32,A34:J35,A36:J37,A38:J38,A40:J40" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0.599993896298105
    End With
	
	'selects all Odd Rows and paints them a lighter blue
        Range( _
        "A15:J15,A17:J17,A19:J19,A21:J21,A23:J23,A25:J25,A27:J27,A29:J29,A31:J31,A33:J33,A35:J35,A37:J37,A39:J39" _
        ).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent5
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0.799981688894314
    End With
	
	

	
	'Selects the date and moves it over to the next week.
    Sheet1.Select
    Range("E5").Select
    Selection.Copy
    Range("E4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Dialogs(xlDialogSaveAs).Show
    
    End If
    
End Sub
 
Upvote 0
I'm using 2007.

Maybe it's something I am not doing right. Let me work on it a bit more later tonight and I will let you know what I find out.

thanks for all the help guys
Be back shortly
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Protecting all cells in a sheet based on .TintAndShade
Replies
4
Views
101
Peter_SSs
Peter_SSs
T
  • Question Question
Filling alternate cells with two colours?
Replies
3
Views
487
Joe4
Joe4
J
  • Question Question
Macro to affect cell background color
Replies
3
Views
416
mumps
mumps
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
J
  • Question Question
Color fill cells surrounding shapes
Replies
9
Views
656
julhs
J

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top