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]

Not sure if you still need help on this, but if so how about changing the countif to

If WorksheetFunction.CountIf(Range("C1:C3000").SpecialCells(xlVisible), "NO") > 0 Then

 

[Nerdalert314]

Not sure if you still need help on this, but if so how about changing the countif to

If WorksheetFunction.CountIf(Range("C1:C3000").SpecialCells(xlVisible), "NO") > 0 Then

Ill try that. Thank you!

 

[Peter_SSs]

.. and if you still need help after that, where is Table3 on the sheet?
I'm trying to understand the relationship (if any) between Table3 and other things in your code like Columns("A:A") and Range("C1:C3000")

 

[Nerdalert314]

Hello,

That did not work. So this is just a portion of a Macro I'm using. Everything in this portion of the code if referring to table 3. Column's A:A and range C1:C3000 are referring to column A and C of table 3. I record most of the macro and then edit it. I'm not the best at writing them yet so this may be incorrect.

Here the test code a just tried.

Sub TEST()
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=4, Criteria1:= _
        "<=" & Date, Operator:=xlAnd
 
If WorksheetFunction.CountIf(Range("C1:C3000").SpecialCells(xlVisible), "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
'
End Sub

 

[Fluff]

In what way doesn't it work?
If you step through the code using F8, does the table get filtered correctly?

 

[Nerdalert314]

I get an error on line:

If WorksheetFunction.CountIf(Range("C1:C3000").SpecialCells(xlVisible), "NO") > 0 Then

Run-time error '1004':
Unable to get the CountIf property of the WorksheetFunction class

 

[Fluff]

Whereabouts in the sheet is your table?

 

[Nerdalert314]

=$A$1:$L$16

Rows will change everyday but the headers are always in A1:L1.

 

[Fluff]

Try

Dim Qty As Long
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=4, Criteria1:= _
        "=" & Date, Operator:=xlAnd
On Error Resume Next
Qty = WorksheetFunction.CountIf(Range("C1:C8000").SpecialCells(xlVisible), "NO")
On Error GoTo 0
If Qty > 0 Then
    ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=3, Criteria1:= _
        "=NO"