Dan's Macro

Sub Gadget()
'
' Gadget Macro
' Placement-Creative Assignment Template Aid
'
' Keyboard Shortcut: Ctrl+g
'
    Range("A:J").Select
    Selection.AutoFilter
    ActiveSheet.Range("A:J").AutoFilter Field:=3, Criteria1:= _
        "<>*TRACKING*", Operator:=xlAnd
    ActiveSheet.Range("A:J").AutoFilter Field:=5, Criteria1:="=*DCM", _
        Operator:=xlAnd
    ActiveSheet.Range("A:J").AutoFilter Field:=6, Criteria1:="=*OBA", _
        Operator:=xlAnd
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A:J").AutoFilter Field:=5, Criteria1:="=*OBA", _
        Operator:=xlAnd
    ActiveSheet.Range("A:J").AutoFilter Field:=6, Criteria1:="<>*OBA", _
        Operator:=xlAnd
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A:J").AutoFilter Field:=6
    ActiveSheet.Range("A:J").AutoFilter Field:=5
    ActiveSheet.Range("A:J").AutoFilter Field:=3
    Sheets.Add after:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Reference"
    ActiveCell.FormulaR1C1 = "DESKTOP"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "MOBILE"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "TABLET"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "SOCIAL"
    Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("B1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("C1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("D1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Sheets("PlacementCreativeAssignments").Select
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(VLOOKUP(E2,Reference!$A:$A,1,FALSE)>0,1,0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(VLOOKUP(E2,Reference!$B:$B,1,FALSE)>0,1,0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(VLOOKUP(E2,Reference!$C:$C,1,FALSE)>0,1,0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(VLOOKUP(E2,Reference!$D:$D,1,FALSE)>0,1,0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("E1").Select
End Sub