Hello,
I recorded a macro and am getting a debug error when I get to a particular spot. The debug error says "Subscript out of range". The error is coming at the "Pattern Color Index"? Honestly, I don't know what it is supposed to be doing at this point, but I do know at some point, I am trying to copy the formatting from cell M2 to N2 before I place the border in M2.
I am including the entire macro below. Yyes, I know it's long and there are probably a lot of "short cuts" that can be put here, I but I don't know how to write code -- I can only record .
I recorded a macro and am getting a debug error when I get to a particular spot. The debug error says "Subscript out of range". The error is coming at the "Pattern Color Index"? Honestly, I don't know what it is supposed to be doing at this point, but I do know at some point, I am trying to copy the formatting from cell M2 to N2 before I place the border in M2.
Code:
Range("M2").Select
ActiveCell.FormulaR1C1 = "Clock In"
Range("N2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
I am including the entire macro below. Yyes, I know it's long and there are probably a lot of "short cuts" that can be put here, I but I don't know how to write code -- I can only record .
Code:
Sheets("HGS").Select
Cells.Select
Selection.Copy
Sheets("Reconcillation").Select
Cells.Select
Range("B1").Activate
ActiveSheet.Paste
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 1
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "HGS"
Range("A1:L1").Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Calibri"
.Size = 22
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("M1").Select
ActiveCell.FormulaR1C1 = "Protos"
Range("M1:O1").Select
With Selection.Font
.Name = "Calibri"
.Size = 22
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M2").Select
ActiveCell.FormulaR1C1 = "Clock In"
Range("N2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Clock Out"
Range("O2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Ttl Hours"
Range("M1:M3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("M3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Protos!C1:C16,11,FALSE)"
Columns("M:M").Select
Selection.NumberFormat = "h:mm:ss;@"
Range("M3").Select
Selection.AutoFill Destination:=Range("M3:M67")
Range("M3:M67").Select
Range("N3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Protos!C1:C16,12,FALSE)"
Columns("N:N").Select
Selection.NumberFormat = "h:mm:ss;@"
Range("N3").Select
Selection.AutoFill Destination:=Range("N3:N67")
Range("N3:N67").Select
Range("O3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Protos!C1:C16,15,FALSE)"
Range("O3").Select
Selection.AutoFill Destination:=Range("O3:O67")
Range("O3:O67").Select
Range("J2:K2").Select
Selection.Copy
Range("N2:O2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("P1").Select
ActiveCell.FormulaR1C1 = "Rounded Hours"
Range("P1:S1").Select
With Selection.Font
.Name = "Calibri"
.Size = 22
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("P2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "HGS In"
Range("Q2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "HGS Out"
Range("R2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Protos In"
Range("S2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Protos Out"
Range("N2").Select
Selection.Copy
Range("P2:S2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("P1:P3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("P3").Select
ActiveCell.FormulaR1C1 = "=MROUND((RC[-7])*24,0.25)"
Range("P3").Select
Selection.AutoFill Destination:=Range("P3:P67")
Range("P3:P67").Select
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=MROUND((RC[-7])*24,0.25)"
Range("Q3").Select
Selection.AutoFill Destination:=Range("Q3:Q67")
Range("Q3:Q67").Select
Range("R3").Select
ActiveCell.FormulaR1C1 = "=MROUND((RC[-5])*24,0.25)"
Range("R3").Select
Selection.AutoFill Destination:=Range("R3:R67")
Range("R3:R67").Select
Range("S3").Select
ActiveCell.FormulaR1C1 = "=MROUND((RC[-5])*24,0.25)"
Range("S3").Select
Selection.AutoFill Destination:=Range("S3:S67")
Range("S3:S67").Select
Range("T1").Select
ActiveCell.FormulaR1C1 = "Actual Hours"
Range("T1:U1").Select
With Selection.Font
.Name = "Calibri"
.Size = 22
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
ActiveWindow.SmallScroll ToRight:=1
Range("T2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "h:mm:ss;@"
ActiveCell.FormulaR1C1 = "HGS"
Range("U2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "h:mm:ss;@"
ActiveCell.FormulaR1C1 = "Protos"
Range("S2").Select
Selection.Copy
Range("T2:U2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("T1:T3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("U1:U3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("T3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-4]"
Range("U3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-3]"
Range("U2:U3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("V2").Select
Selection.Style = "Normal_HGS_1"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = -7
.ColorIndex = 15
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "h:mm:ss;@"
ActiveCell.FormulaR1C1 = "Variance"
Range("U2").Select
Selection.Copy
Range("V2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("V1:V3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("V3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[-1]-RC[-2]"
Range("T3").Select
Selection.AutoFill Destination:=Range("T3:T67")
Range("T3:T67").Select
Range("U3").Select
Selection.AutoFill Destination:=Range("U3:U67")
Range("U3:U67").Select
Range("V3").Select
Selection.AutoFill Destination:=Range("V3:V67")
Range("V3:V67").Select
Columns("P:V").Select
Selection.NumberFormat = "0.00"
Rows("2:2").Select
Range("B2").Activate
Selection.AutoFilter
Range("C3").Select
ActiveWindow.FreezePanes = True
Range("A3").Select