Multiple Conditions on table with If Statement in Macro

N

Nerdalert314

New Member
Joined
Mar 28, 2018
Messages
23
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.


Code: 
[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]
 
Sort by date Sort by votes
Now it always skips. It is acting like quantity is always 0.

Code: 
Sub test()
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"
 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
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Once the first filter is in place, do you have any visible cells in col C with a value of NO?
 
Upvote 0
Check that they don't have any leading or trailing spaces.
 
Upvote 0
There isn't :confused: Here are columns C and D
[TABLE="width: 170"]
<colgroup><col width="145" style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <tbody>[TR]
[TD="class: xl70, width: 145"]Complete?[/TD]
[TD="class: xl67, width: 81"]Eligibility[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #D3F5F8"]YES[/TD]
[TD="class: xl65, width: 81"]02/27/18[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]NO[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent"]02/27/18[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #D3F5F8"]YES[/TD]
[TD="class: xl65, width: 81"]03/08/18[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]YES[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent"]03/09/18[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #D3F5F8"]YES[/TD]
[TD="class: xl65, width: 81"]03/15/18[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]Researched Incomplete[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent"]03/22/18[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #D3F5F8"]YES[/TD]
[TD="class: xl65, width: 81"]03/23/18[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]NO[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent"]03/30/18[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #D3F5F8"]YES[/TD]
[TD="class: xl65, width: 81"]04/02/18[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]NO[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent"]04/02/18[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #D3F5F8"]NO[/TD]
[TD="class: xl65, width: 81"]04/26/18[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]NO[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent"]05/07/18[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #D3F5F8"]NO[/TD]
[TD="class: xl65, width: 81"]05/10/18[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]NO[/TD]
[TD="class: xl66, width: 81, bgcolor: transparent"]05/22/18[/TD]
[/TR]
[TR]
[TD="class: xl71"]NO[/TD]
[TD="class: xl68, width: 81"]03/31/28[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
I'm guessing it's got something to do with the fact that it's a table.
As I don't have much experience with tables, I'm not sure I can help much more.
 
Upvote 0
Does this work for you?
Code: 
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
    Range("Table3").Select
    Selection.Style = "Accent4"
    Ws.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
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
763
GardianAngelUK
G
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
399
willow1985
willow1985
S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
556
sspatriots
S
A
  • Question Question
Save as dialogue box for excel, but pre populating filename from source word document
Replies
8
Views
283
RoryA
RoryA
K
  • Solved
formating question
Replies
3
Views
324
Micron
M

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

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