Multiple Conditions on table with If Statement in Macro

[Nerdalert314]

Hello all!

I am trying to use and If statement with a table. If eligibility is before today and if complete says no I am wanting to highlight. My problem is when there is nothing to highlight it is highlighting the whole table. It is not proceeding on to the else statement. On another table I am wanting to highlight anything for this month and next month that says no. Same problem when nothing says no. Any help would be appreciated! .... I do have some repetitive stuff in there trying to get this to work. I am very new to even reading code so don't expect this to be correct.

[COLOR=#0000ff]ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=4, Criteria1:= _
        "<=" & Date, Operator:=xlAnd
 
If Evaluate(WorksheetFunction.CountIf(Range("C1:C3000"), "NO")) > 0 Then
    ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=3, Criteria1:= _
        "=NO"
    Range("Table3").Select
    Selection.Style = "Accent4"
    ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=3
    Columns("A:A").Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").Select
    With Selection.Interior
        .PatternThemeColor = xlThemeColorAccent3
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
        Range("Table3").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=3
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=4
 
 Range("B2").Select
 
Else
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=3
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=4
    Range("B2").Select
End If[/COLOR]

 

[Fluff]

Ok, this is a slightly cleaned up version of your code. It can probably be cleaned up a bit more, but without seeing your data I can't be sure

Sub TEST()
   Dim Qty As Long
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   
   Ws.ListObjects("Table3").Range.AutoFilter Field:=4, Criteria1:= _
      "<=" & Date, Operator:=xlAnd
   Ws.ListObjects("Table3").Range.AutoFilter Field:=3, Criteria1:= _
      "=NO"
   On Error Resume Next
   Qty = Ws.ListObjects("Table3").DataBodyRange.SpecialCells(xlVisible).Count
   On Error GoTo 0
   
   If Qty > 0 Then
      With Ws.ListObjects("Table3").DataBodyRange
         .Style = "Accent4"
         .AutoFilter Field:=3
         With .Columns(1)
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            With .Interior
               .PatternThemeColor = xlThemeColorAccent3
               .ThemeColor = xlThemeColorDark1
               .TintAndShade = 0
               .PatternTintAndShade = 0
            End With
            With .Font
               .ThemeColor = xlThemeColorDark1
               .TintAndShade = 0
            End With
         End With
         With .Font
            .Name = "Arial"
            .size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
         End With
         .VerticalAlignment = xlBottom
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
      End With
      
      Ws.ListObjects("Table3").Range.AutoFilter Field:=3
      Ws.ListObjects("Table3").Range.AutoFilter Field:=4
      
      Range("B2").Select
      
   Else
      Ws.ListObjects("Table3").Range.AutoFilter Field:=3
      Ws.ListObjects("Table3").Range.AutoFilter Field:=4
      Range("B2").Select
   End If

End Sub

 

[Nerdalert314]

Thanks for your help! I believe this is all I need. Column A needs to not wraptext and be filled white. The whole table needs to be Arial size 10 font aligned xlbottom.

Sub TEST()
   Dim Qty As Long
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   
   Ws.ListObjects("Table3").Range.AutoFilter Field:=4, Criteria1:= _
      "<=" & Date, Operator:=xlAnd
   Ws.ListObjects("Table3").Range.AutoFilter Field:=3, Criteria1:= _
      "=NO"
   On Error Resume Next
   Qty = Ws.ListObjects("Table3").DataBodyRange.SpecialCells(xlVisible).Count
   On Error GoTo 0
   
   If Qty > 0 Then
      With Ws.ListObjects("Table3").DataBodyRange
         .Style = "Accent4"
         .AutoFilter Field:=3
         With .Columns(1)
            .WrapText = False
         With .Interior
               .TintAndShade = 0
            End With
            With .Font
               .TintAndShade = 0
            End With
         End With
         With .Font
            .Name = "Arial"
            .Size = 10
         End With
         .VerticalAlignment = xlBottom
      End With
      
      Ws.ListObjects("Table3").Range.AutoFilter Field:=3
      Ws.ListObjects("Table3").Range.AutoFilter Field:=4
      
      Range("B2").Select
      
   Else
      Ws.ListObjects("Table3").Range.AutoFilter Field:=3
      Ws.ListObjects("Table3").Range.AutoFilter Field:=4
      Range("B2").Select
   End If
End Sub

 

[Fluff]

Glad to help & thanks for the feedback