nikki.tay81
New Member
I have a macro I created to take info and give me average, standard deviation, the sum of both of those. Then if and statement to give me particular wording with conditional formating. Right now I only have it set to work for columns B-K with all info in set cells. What I work on is in sets of columns by 10s so I want this macro to work all the way across without my having to copr and paste the macro for each set of 10. Here is what I have below:
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("C1048574").Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Range("C1048572").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1048570]C:R[-1]C)"
Selection.Copy
Range("D1048572:I1048572").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J1048572").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Range("J1048572").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1048570]C:R[-1]C)"
Range("J1048572").Select
Selection.NumberFormat = "0"
Range("C1048573").Select
ActiveWindow.SmallScroll Down:=4
ActiveCell.FormulaR1C1 = "=STDEV(R[-1048571]C:R[-2]C)"
Selection.Copy
Range("D1048573:I1048573").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1048573").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Style = "Percent"
Range("C1048574").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C[6])"
Range("D1048574").Select
ActiveCell.FormulaR1C1 = "=R[-2]C[6]"
Range("E1048574").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>5%,RC[-1]),""exclude"")"
Range("E1048574").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""misalignment"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B1048574").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("B1").Select
Selection.Copy
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("B1048574").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("B1048574"), DataType:= _
xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True _
, OtherChar:="
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("C1048574").Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Range("C1048572").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1048570]C:R[-1]C)"
Selection.Copy
Range("D1048572:I1048572").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J1048572").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Range("J1048572").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1048570]C:R[-1]C)"
Range("J1048572").Select
Selection.NumberFormat = "0"
Range("C1048573").Select
ActiveWindow.SmallScroll Down:=4
ActiveCell.FormulaR1C1 = "=STDEV(R[-1048571]C:R[-2]C)"
Selection.Copy
Range("D1048573:I1048573").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1048573").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Style = "Percent"
Range("C1048574").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C[6])"
Range("D1048574").Select
ActiveCell.FormulaR1C1 = "=R[-2]C[6]"
Range("E1048574").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>5%,RC[-1]),""exclude"")"
Range("E1048574").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""misalignment"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B1048574").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("B1").Select
Selection.Copy
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("B1048574").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("B1048574"), DataType:= _
xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True _
, OtherChar:="