every time I run this code the auto filter only filters 1 column any ideals

A

alan myers

Board Regular
Joined
Oct 31, 2017
Messages
119
Office Version
  1. 365
Platform
  1. Windows
VBA Code: 
Sub FRANCHISEBALL()



'*************************
' color background
'*************************'

    Cells.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With


'*************************
' rename hearders
'*************************
  
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Main/RLINK"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "NAME"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "TEAM"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "THROWS"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "SEASON/AB"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "HITS"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "RBI"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "SLG"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "2B"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "3B"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "HR"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "BB"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "RUNS"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "TB"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "SB"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "SB%"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "OBP"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "OPS"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "XBH"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "AB/HR"
    Range("AD1").Select
    ActiveCell.FormulaR1C1 = "ISO"
    Range("AE1").Select
    ActiveCell.FormulaR1C1 = "RC"
    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "E"
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "CAREER/SEASONS"
    Range("AH1").Select
    ActiveCell.FormulaR1C1 = "TEAM"
    Range("AI1").Select
    ActiveCell.FormulaR1C1 = "AB"
    Range("AJ1").Select
    ActiveCell.FormulaR1C1 = "HITS"
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("AL1").Select
    ActiveCell.FormulaR1C1 = "RBI"
    Range("AM1").Select
    ActiveCell.FormulaR1C1 = "SLG"
    Range("AN1").Select
    ActiveCell.FormulaR1C1 = "2B"
    Range("AO1").Select
    ActiveCell.FormulaR1C1 = "3B"
    Range("AP1").Select
    ActiveCell.FormulaR1C1 = "HR"
    Range("AQ1").Select
    ActiveCell.FormulaR1C1 = "SB"
    Range("AR1").Select
    ActiveCell.FormulaR1C1 = "MINORS/AB"
    Range("AS1").Select
    ActiveCell.FormulaR1C1 = "HITS"
    Range("AT1").Select
    ActiveCell.FormulaR1C1 = "AVG"
    Range("AU1").Select
    ActiveCell.FormulaR1C1 = "RBI"
    Range("AV1").Select
    ActiveCell.FormulaR1C1 = "SLG"
    Range("AW1").Select
    ActiveCell.FormulaR1C1 = "2B"
    Range("AX1").Select
    ActiveCell.FormulaR1C1 = "3B"
    Range("AY1").Select
    ActiveCell.FormulaR1C1 = "HR"
    Range("AZ1").Select
    ActiveCell.FormulaR1C1 = "BB"
    Range("BA1").Select
    ActiveCell.FormulaR1C1 = "TB"
    Range("BB1").Select
    ActiveCell.FormulaR1C1 = "PA"
    Range("BC1").Select
    ActiveCell.FormulaR1C1 = "OBP"
    Range("BD1").Select
    ActiveCell.FormulaR1C1 = "OPS"
    Range("BE1").Select
    ActiveCell.FormulaR1C1 = "BB/PA"
    Range("BF1").Select
    ActiveCell.FormulaR1C1 = "XBH"
    Range("BG1").Select
    ActiveCell.FormulaR1C1 = "AB/HR"
    Range("BH1").Select
    ActiveCell.FormulaR1C1 = "ISO"
    Range("BI1").Select
    ActiveCell.FormulaR1C1 = "RC"
    Range("BJ1").Select
    ActiveCell.FormulaR1C1 = "SCOUT PIT/CONTROL"
    Range("BK1").Select
    ActiveCell.FormulaR1C1 = "MOVEMENT"
    Range("BH8:BJ16").Select
    Range("BL1").Select
    ActiveCell.FormulaR1C1 = "VELOCITY"
    Range("BM1").Select
    ActiveCell.FormulaR1C1 = "STAMINA"
    Range("BN1").Select
    ActiveCell.FormulaR1C1 = "POWER"
    Range("BO1").Select
    ActiveCell.FormulaR1C1 = "SCOUTH-CONTACT"
    Range("BN1").Select
    ActiveCell.FormulaR1C1 = "SCOUT HIT/POWER"
    Range("BO1").Select
    ActiveCell.FormulaR1C1 = "CONTACT"
    Range("BP1").Select
    ActiveCell.FormulaR1C1 = "SPEED"
    Range("BQ1").Select
    ActiveCell.FormulaR1C1 = "DEFENSE"
    Range("BR1").Select
    ActiveCell.FormulaR1C1 = "SCOUT RANGE/LF"
    Range("BS1").Select
    ActiveCell.FormulaR1C1 = "LC"
    Range("BT1").Select
    ActiveCell.FormulaR1C1 = "C"
    Range("BU1").Select
    ActiveCell.FormulaR1C1 = "RC"
    Range("BV1").Select
    ActiveCell.FormulaR1C1 = "RF"
    Range("BV2").Select


'*************************
' format cells
'*************************

    Cells.Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 2
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


'*************************
' MOVE NAMES COLUMN
'*************************

    Columns("B:B").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-1],RC[-2])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C87354")
    Range("C2:C87354").Select



'*************************
' COLOR NAMES
'*************************

    Columns("C:C").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


'*************************
' HIDE ROWS
'*************************

    Columns("A:B").Select
    Range("B1").Activate
    Selection.EntireColumn.Hidden = True


'*************************
' color hearders
'*************************

    Range("A1:M1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    Range("N1:AG1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
 
    Range("AH1:AR1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    Range("AS1:BJ1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
  
    Range("BK1:BN1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BO1:BR1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("BS1:BW1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

  
'*************************
' CHANGE NAME HEADER COLOR
'*************************'

    Range("C1").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""NAME"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


'*************************
' FORMAT NUMBERS
'*************************

    Columns("F:F").Select
    Selection.NumberFormat = "0"
    Columns("G:G").Select
    Selection.NumberFormat = "0"
    Columns("J:J").Select
    Selection.NumberFormat = "0.000"
    Columns("K:K").Select
    Selection.NumberFormat = "0.000"
    Columns("L:L").Select
    Selection.NumberFormat = "0"
    Columns("N:N").Select
    Selection.NumberFormat = "0"
    Columns("O:O").Select
    Selection.NumberFormat = "0"
    Columns("P:P").Select
    Selection.NumberFormat = "0.000"
    Columns("Q:Q").Select
    Selection.NumberFormat = "0"
    Columns("R:R").Select
    Selection.NumberFormat = "0.000"
    Columns("S:S").Select
    Selection.NumberFormat = "0"
    Columns("T:T").Select
    Selection.NumberFormat = "0"
    Columns("U:U").Select
    Selection.NumberFormat = "0"
    Columns("V:V").Select
    Selection.NumberFormat = "0"
    Columns("W:W").Select
    Selection.NumberFormat = "0"
    Columns("X:X").Select
    Selection.NumberFormat = "0"
    Columns("Y:Y").Select
    Selection.NumberFormat = "0"
    Columns("Z:Z").Select
    Selection.NumberFormat = "0"
    Columns("AA:AA").Select
    Selection.NumberFormat = "0.000"
    Columns("AB:AB").Select
    Selection.NumberFormat = "0.000"
    Columns("AC:AC").Select
    Selection.NumberFormat = "0"
    Columns("AD:AD").Select
    Selection.NumberFormat = "0.00"
    Columns("AE:AE").Select
    Selection.NumberFormat = "0.000"
    Columns("AF:AF").Select
    Selection.NumberFormat = "0.00"
    Columns("AG:AG").Select
    Selection.NumberFormat = "0"
    Columns("AH:AH").Select
    Selection.NumberFormat = "0"
    Columns("AJ:AJ").Select
    Selection.NumberFormat = "0"
    Columns("AK:AK").Select
    Selection.NumberFormat = "0"
    Columns("AL:AL").Select
    Selection.NumberFormat = "0.000"
    Columns("AM:AM").Select
    Selection.NumberFormat = "0"
    Columns("AN:AN").Select
    Selection.NumberFormat = "0.000"
    Columns("AO:AO").Select
    Selection.NumberFormat = "0"
    Columns("AP:AP").Select
    Selection.NumberFormat = "0"
    Columns("AQ:AQ").Select
    Selection.NumberFormat = "0"
    Columns("AR:AR").Select
    Selection.NumberFormat = "0"
    Columns("AS:AS").Select
    Selection.NumberFormat = "0"
    Columns("AT:AT").Select
    Selection.NumberFormat = "0"
    Columns("AU:AU").Select
    Selection.NumberFormat = "0.000"
    Columns("AV:AV").Select
    Selection.NumberFormat = "0"
    Columns("AW:AW").Select
    Selection.NumberFormat = "0.000"
    Columns("AX:AX").Select
    Selection.NumberFormat = "0"
    Columns("AY:AY").Select
    Selection.NumberFormat = "0"
    Columns("AZ:AZ").Select
    Selection.NumberFormat = "0"
    Columns("BA:BA").Select
    Selection.NumberFormat = "0"
    Columns("BB:BB").Select
    Selection.NumberFormat = "0"
    Columns("BC:BC").Select
    Selection.NumberFormat = "0"
    Columns("BD:BD").Select
    Selection.NumberFormat = "0.000"
    Columns("BE:BE").Select
    Selection.NumberFormat = "0.000"
    Columns("BF:BF").Select
    Selection.NumberFormat = "0.000"
    Columns("BG:BG").Select
    Selection.NumberFormat = "0"
    Columns("BH:BH").Select
    Selection.NumberFormat = "0.00"
    Columns("BI:BI").Select
    Selection.NumberFormat = "0.000"
    Columns("BJ:BJ").Select
    Selection.NumberFormat = "0.00"
    Columns("BK:BK").Select
    Selection.NumberFormat = "0"
    Columns("BL:BL").Select
    Selection.NumberFormat = "0"
    Columns("BM:BM").Select
    Selection.NumberFormat = "0"
    Columns("BN:BN").Select
    Selection.NumberFormat = "0"
    Columns("BO:BO").Select
    Selection.NumberFormat = "0"
    Columns("BP:BP").Select
    Selection.NumberFormat = "0"
    Columns("BQ:BQ").Select
    Selection.NumberFormat = "0"
    Columns("BR:BR").Select
    Selection.NumberFormat = "0"
    Columns("BS:BS").Select
    Selection.NumberFormat = "0"
    Columns("BT:BT").Select
    Selection.NumberFormat = "0"
    Columns("BU:BU").Select
    Selection.NumberFormat = "0"
    Columns("BV:BV").Select
    Selection.NumberFormat = "0"
    Columns("BW:BW").Select
    Selection.NumberFormat = "0"


'***************************
' FREEZ FRAME
'***************************

    Range("D2").Select
    With ActiveWindow
        .SplitColumn = 1
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveWindow.FreezePanes = False
    ActiveWindow.FreezePanes = True


'*************************
' TEXXT TO COLUMN
'*************************

    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("J:J").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("K:K").Select
    Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("L:L").Select
    Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("N:N").Select
    Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("O:O").Select
    Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("P:P").Select
    Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Q:Q").Select
    Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("R:R").Select
    Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("S:S").Select
    Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("T:T").Select
    Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("U:U").Select
    Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("V:V").Select
    Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("W:W").Select
    Selection.TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("X:X").Select
    Selection.TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Y:Y").Select
    Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
 
    Columns("Z:Z").Select
    Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AA:AA").Select
    Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AB:AB").Select
    Selection.TextToColumns Destination:=Range("AB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AC:AC").Select
    Selection.TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AD:AD").Select
    Selection.TextToColumns Destination:=Range("AD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AE:AE").Select
    Selection.TextToColumns Destination:=Range("AE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AF:AF").Select
    Selection.TextToColumns Destination:=Range("AF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AG:AG").Select
    Selection.TextToColumns Destination:=Range("AG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AH:AH").Select
    Selection.TextToColumns Destination:=Range("AH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AJ:AJ").Select
    Selection.TextToColumns Destination:=Range("AJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AK:AK").Select
    Selection.TextToColumns Destination:=Range("AK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AL:AL").Select
    Selection.TextToColumns Destination:=Range("AL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AM:AM").Select
    Selection.TextToColumns Destination:=Range("AM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AN:AN").Select
    Selection.TextToColumns Destination:=Range("AN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AO:AO").Select
    Selection.TextToColumns Destination:=Range("AO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AP:AP").Select
    Selection.TextToColumns Destination:=Range("AP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AQ:AQ").Select
    Selection.TextToColumns Destination:=Range("AQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AR:AR").Select
    Selection.TextToColumns Destination:=Range("AR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AS:AS").Select
    Selection.TextToColumns Destination:=Range("AS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AT:AT").Select
    Selection.TextToColumns Destination:=Range("AT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AU:AU").Select
    Selection.TextToColumns Destination:=Range("AU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AV:AV").Select
    Selection.TextToColumns Destination:=Range("AV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AW:AW").Select
    Selection.TextToColumns Destination:=Range("AW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AX:AX").Select
    Selection.TextToColumns Destination:=Range("AX1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AY:AY").Select
    Selection.TextToColumns Destination:=Range("AY1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AZ:AZ").Select
    Selection.TextToColumns Destination:=Range("AZ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BA:BA").Select
    Selection.TextToColumns Destination:=Range("BA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BB:BB").Select
    Selection.TextToColumns Destination:=Range("BB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BC:BC").Select
    Selection.TextToColumns Destination:=Range("BC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BD:BD").Select
    Selection.TextToColumns Destination:=Range("BD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BE:BE").Select
    Selection.TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BE:BE").Select
    Selection.TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BF:BF").Select
    Selection.TextToColumns Destination:=Range("BF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BG:BG").Select
    Selection.TextToColumns Destination:=Range("BG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BH:BH").Select
    Selection.TextToColumns Destination:=Range("BH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BI:BI").Select
    Selection.TextToColumns Destination:=Range("BI1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BJ:BJ").Select
    Selection.TextToColumns Destination:=Range("BJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BK:BK").Select
    Selection.TextToColumns Destination:=Range("BK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BL:BL").Select
    Selection.TextToColumns Destination:=Range("BL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BM:BM").Select
    Selection.TextToColumns Destination:=Range("BM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BN:BN").Select
    Selection.TextToColumns Destination:=Range("BN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BO:BO").Select
    Selection.TextToColumns Destination:=Range("BO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BP:BP").Select
    Selection.TextToColumns Destination:=Range("BP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BQ:BQ").Select
    Selection.TextToColumns Destination:=Range("BQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BR:BR").Select
    Selection.TextToColumns Destination:=Range("BR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BS:BS").Select
    Selection.TextToColumns Destination:=Range("BS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BT:BT").Select
    Selection.TextToColumns Destination:=Range("BT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BU:BU").Select
    Selection.TextToColumns Destination:=Range("BU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BV:BV").Select
    Selection.TextToColumns Destination:=Range("BV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BW:BW").Select
    Selection.TextToColumns Destination:=Range("BW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

  
'*************************
' auto filter
'*************************

    Selection.AutoFilter
  
  
'*************************
' spacing
'*************************

'    Selection.ColumnWidth = 32.82
    Selection.ColumnWidth = 50.36
    Cells.EntireColumn.AutoFit


'*************************
' FORMATTING SEARCH
'*************************

    Columns("BK:BN").Select
    Range("BN1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=89"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("BO:BR").Select
    Range("BR1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=89"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Columns("BS:BW").Select
    Range("BW1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=5"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
  


End Sub
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hi alan myers,

relevant part of code

Rich (BB code): 
    Columns("BW:BW").Select
    Selection.TextToColumns Destination:=Range("BW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

  
'*************************
' auto filter
'*************************

    Selection.AutoFilter

The last Selection was for Column BW, so any new commands will rely on that range.

The Filter command could be changed to

VBA Code: 
    'set AutoFilter for all cells in Row 1 until last filled cell from the left
    Range("A1", Cells(1, Columns.Count).End(xlToLeft)).AutoFilter

Please find a version without Select, Selection or Activate here (not tested):

VBA Code: 
Sub FRANCHISEBALL()
' https://www.mrexcel.com/board/threads/every-time-i-run-this-code-the-auto-filter-only-filters-1-column-any-ideals.1224315/
' Updated: 20221210
' By:      HaHoBe
' Reason:  Reworked Code from Macro Recorder

'works on the ActiveSheet

Application.ScreenUpdating = False

'*************************
' color background
'*************************'
'*************************
' format cells  <--- moved and merged
'*************************

  With Cells
    With .Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    With .Font
      .ThemeColor = xlThemeColorDark1
      .TintAndShade = 0
    End With
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 2
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With

'*************************
' rename hearders
'*************************
  
    Range("A1").Value = "Main/RLINK"
    Range("B1").Value = "NAME"
    Range("C1").Value = "TEAM"
    Range("G1").Value = "THROWS"
    Range("M1").Value = "SEASON/AB"
    Range("N1").Value = "HITS"
    Range("O1").Value = "AVG"
    Range("P1").Value = "RBI"
    Range("Q1").Value = "SLG"
    Range("R1").Value = "2B"
    Range("S1").Value = "3B"
    Range("T1").Value = "HR"
    Range("U1").Value = "BB"
    Range("V1").Value = "RUNS"
    Range("W1").Value = "TB"
    Range("X1").Value = "SB"
    Range("Y1").Value = "SB%"
    Range("Z1").Value = "OBP"
    Range("AA1").Value = "OPS"
    Range("AB1").Value = "XBH"
    Range("AC1").Value = "AB/HR"
    Range("AD1").Value = "ISO"
    Range("AE1").Value = "RC"
    Range("AF1").Value = "E"
    Range("AG1").Value = "CAREER/SEASONS"
    Range("AH1").Value = "TEAM"
    Range("AI1").Value = "AB"
    Range("AJ1").Value = "HITS"
    Range("AK1").Value = "AVG"
    Range("AL1").Value = "RBI"
    Range("AM1").Value = "SLG"
    Range("AN1").Value = "2B"
    Range("AO1").Value = "3B"
    Range("AP1").Value = "HR"
    Range("AQ1").Value = "SB"
    Range("AR1").Value = "MINORS/AB"
    Range("AS1").Value = "HITS"
    Range("AT1").Value = "AVG"
    Range("AU1").Value = "RBI"
    Range("AV1").Value = "SLG"
    Range("AW1").Value = "2B"
    Range("AX1").Value = "3B"
    Range("AY1").Value = "HR"
    Range("AZ1").Value = "BB"
    Range("BA1").Value = "TB"
    Range("BB1").Value = "PA"
    Range("BC1").Value = "OBP"
    Range("BD1").Value = "OPS"
    Range("BE1").Value = "BB/PA"
    Range("BF1").Value = "XBH"
    Range("BG1").Value = "AB/HR"
    Range("BH1").Value = "ISO"
    Range("BI1").Value = "RC"
    Range("BJ1").Value = "SCOUT PIT/CONTROL"
    Range("BK1").Value = "MOVEMENT"
    Range("BL1").Value = "VELOCITY"
    Range("BM1").Value = "STAMINA"
    Range("BN1").Value = "POWER"
    Range("BO1").Value = "SCOUTH-CONTACT"
    Range("BN1").Value = "SCOUT HIT/POWER"
    Range("BO1").Value = "CONTACT"
    Range("BP1").Value = "SPEED"
    Range("BQ1").Value = "DEFENSE"
    Range("BR1").Value = "SCOUT RANGE/LF"
    Range("BS1").Value = "LC"
    Range("BT1").Value = "C"
    Range("BU1").Value = "RC"
    Range("BV1").Value = "RF"


'*************************
' MOVE NAMES COLUMN
'*************************

    Columns("B:B").Copy
    Columns("A:A").Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Range("C2").FormulaR1C1 = "=HYPERLINK(RC[-1],RC[-2])"
    Range("C2").AutoFill Destination:=Range("C2:C87354")


'*************************
' COLOR NAMES
'*************************

    With Columns("C:C").Select
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark1
          .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With

'*************************
' HIDE ROWS   <-- Comment should otell what you do, next command is hiding Columns
'*************************

    Columns("A:B").EntireColumn.Hidden = True


'*************************
' color hearders
'*************************

    With Range("A1:M1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 15773696
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
 
    withRange ("N1:AG1")
      With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      With .Font
          .ColorIndex = xlAutomatic
          .TintAndShade = 0
      End With
    End With
 
    With Range("AH1:AR1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 5287936
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
 
    With Range("AS1:BJ1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 12611584
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
  
    With Range("BK1:BN1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 255
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
    
    With Range("BO1:BR1").Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 49407
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
    
    With Range("BS1:BW1").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

  
'*************************
' CHANGE NAME HEADER COLOR
'*************************'

    With Range("C1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""NAME"""
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With

'*************************
' FORMAT NUMBERS
'*************************
    
    
    Range("F:G,L:L,N:O,Q:Q,S:Z,AC:AC,AG:AH,AJ:AK,AM:AM,AO:AT,AV:AV,AX:BC,BG:BG,BK:BW").NumberFormat = "0"
    Range("J:K,P:P,R:R,AA:AB,AD:AE,AL:AL,AN:AN,AU:AU,AW:AW,BD:BF,BI:BI").NumberFormat = "0.000"
    Range("AF:AF,BH:BH,BJ:BJ").NumberFormat = "0.00"
    
'    Columns("F:G").NumberFormat = "0"
'    Columns("J:K").NumberFormat = "0.000"
'    Columns("L:L").NumberFormat = "0"
'    Columns("N:O").NumberFormat = "0"
'    Columns("P:P").NumberFormat = "0.000"
'    Columns("Q:Q").NumberFormat = "0"
'    Columns("R:R").NumberFormat = "0.000"
'    Columns("S:Z").NumberFormat = "0"
'    Columns("AA:AB").NumberFormat = "0.000"
'    Columns("AC:AC").NumberFormat = "0"
'    Columns("AD:AE").NumberFormat = "0.000"
'    Columns("AF:AF").NumberFormat = "0.00"
'    Columns("AG:AH").NumberFormat = "0"
'    Columns("AJ:AK").NumberFormat = "0"
'    Columns("AL:AL").NumberFormat = "0.000"
'    Columns("AM:AM").NumberFormat = "0"
'    Columns("AN:AN").NumberFormat = "0.000"
'    Columns("AO:AT").NumberFormat = "0"
'    Columns("AU:AU").NumberFormat = "0.000"
'    Columns("AV:AV").NumberFormat = "0"
'    Columns("AW:AW").NumberFormat = "0.000"
'    Columns("AX:BC").NumberFormat = "0"
'    Columns("BD:BF").NumberFormat = "0.000"
'    Columns("BG:BG").NumberFormat = "0"
'    Columns("BH:BH").NumberFormat = "0.00"
'    Columns("BI:BI").NumberFormat = "0.000"
'    Columns("BJ:BJ").NumberFormat = "0.00"
'    Columns("BK:BW").NumberFormat = "0"


'***************************
' FREEZ FRAME
'***************************

    With ActiveWindow
      .SplitColumn = 1
      .SplitRow = 1
      .FreezePanes = True
      .FreezePanes = False
      .FreezePanes = True
    End With


'*************************
' TEXXT TO COLUMN
'*************************

    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("K:K").TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("L:L").TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("O:O").TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("P:P").TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Q:Q").TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("R:R").TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("S:S").TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("T:T").TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("U:U").TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("V:V").TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("W:W").TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("X:X").TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("Y:Y").TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
 
    Columns("Z:Z").TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AA:AA").TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AB:AB").TextToColumns Destination:=Range("AB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AC:AC").TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AD:AD").TextToColumns Destination:=Range("AD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AE:AE").TextToColumns Destination:=Range("AE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AF:AF").TextToColumns Destination:=Range("AF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AG:AG").TextToColumns Destination:=Range("AG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AH:AH").TextToColumns Destination:=Range("AH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AJ:AJ").TextToColumns Destination:=Range("AJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AK:AK").TextToColumns Destination:=Range("AK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AL:AL").TextToColumns Destination:=Range("AL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AM:AM").TextToColumns Destination:=Range("AM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AN:AN").TextToColumns Destination:=Range("AN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AO:AO").TextToColumns Destination:=Range("AO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AP:AP").TextToColumns Destination:=Range("AP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AQ:AQ").TextToColumns Destination:=Range("AQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AR:AR").TextToColumns Destination:=Range("AR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AS:AS").TextToColumns Destination:=Range("AS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AT:AT").TextToColumns Destination:=Range("AT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AU:AU").TextToColumns Destination:=Range("AU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AV:AV").TextToColumns Destination:=Range("AV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AW:AW").TextToColumns Destination:=Range("AW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AX:AX").TextToColumns Destination:=Range("AX1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AY:AY").TextToColumns Destination:=Range("AY1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("AZ:AZ").TextToColumns Destination:=Range("AZ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BA:BA").TextToColumns Destination:=Range("BA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BB:BB").TextToColumns Destination:=Range("BB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BC:BC").TextToColumns Destination:=Range("BC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BD:BD").TextToColumns Destination:=Range("BD1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BE:BE").TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BF:BF").TextToColumns Destination:=Range("BF1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BG:BG").TextToColumns Destination:=Range("BG1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BH:BH").TextToColumns Destination:=Range("BH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BI:BI").TextToColumns Destination:=Range("BI1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BJ:BJ").TextToColumns Destination:=Range("BJ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BK:BK").TextToColumns Destination:=Range("BK1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BL:BL").TextToColumns Destination:=Range("BL1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BM:BM").TextToColumns Destination:=Range("BM1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BN:BN").TextToColumns Destination:=Range("BN1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BO:BO").TextToColumns Destination:=Range("BO1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BP:BP").TextToColumns Destination:=Range("BP1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BQ:BQ").TextToColumns Destination:=Range("BQ1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BR:BR").TextToColumns Destination:=Range("BR1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BS:BS").TextToColumns Destination:=Range("BS1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BT:BT").TextToColumns Destination:=Range("BT1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BU:BU").TextToColumns Destination:=Range("BU1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BV:BV").TextToColumns Destination:=Range("BV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("BW:BW").TextToColumns Destination:=Range("BW1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

  
'*************************
' auto filter
'*************************

    'disable any AutoFilter
    ActiveSheet.AutoFilterMode = False
0
  
'*************************
' spacing
'*************************

'    Selection.ColumnWidth = 32.82
'    Selection.ColumnWidth = 50.36
    Cells.EntireColumn.AutoFit


'*************************
' FORMATTING SEARCH
'*************************

    With Range("BN1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=89"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
      End With
      With .FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End If
    
    With Range("BR1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=89"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
      End With
      With .FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 2
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With
    
    With Range("BW1")
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=5"
      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
      End With
      With .FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
      End With
      With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With

    Application.ScreenUpdating = True

End Sub

Ciao,
Holger
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

F
  • Solved
Delimiters in VBA has different result than manual
Replies
7
Views
92
Peter_SSs
Peter_SSs
J
  • Question Question
Text to Columns in Macro
2
Replies
10
Views
630
Alex Blakenburg
A
Z
  • Solved
Date and Time Not Sorting
2
Replies
19
Views
314
MARK858
MARK858
D
  • Question Question
VBA optimization advice
Replies
3
Views
401
bobsan42
bobsan42
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
104
vancher19
V

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

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