Need VBA to Highlight Rows with all N's

[Stephen_IV]

Good morning,

I have over 300,000 rows of data in Columns A-F. The rows have either N or Y in them. What I need to do is to highlight all rows that have just N's in them. I do not want a formula and Code will help! Thanks in advance.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>N</th><th>N</th><th>N</th><th>N</th><th>N</th><th>N</th></tr></thead><tbody>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td></tr>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>Y</td><td>N</td><td>N</td><td>N</td><td>N</td><td>Y</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>Y</td><td>N</td><td>N</td></tr>
<tr><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td><td>N</td></tr>
</tbody></table>

 

[My Aswer Is This]

Highlight entire row or just A to F

 

[My Aswer Is This]

Try this:

Sub Auto_Filter()
Application.ScreenUpdating = False

    With Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="N"
        .AutoFilter Field:=2, Criteria1:="N"
        .AutoFilter Field:=3, Criteria1:="N"
        .AutoFilter Field:=4, Criteria1:="N"
        .AutoFilter Field:=5, Criteria1:="N"
        .AutoFilter Field:=6, Criteria1:="N"
        .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

 

[jim may]

You Could ALSO use conditional Formatting as follows...

1) Hightlight (Select) your Range "A1:F21"
2) Goto ConditionalFormatting, select Use Formula, in the Formula Textbox enter:

=AND(COUNTIF($A1:$F1,"N")=6,COLUMN()>=1,COLUMN()<7)

Select Fill - Say Yellow

OK, OK,

Done

 

[Stephen_IV]

Thanks all for the help. Much appreciated!

 

[My Aswer Is This]

Not sure what approach you took but glad we could help you.

Thanks all for the help. Much appreciated!

 

[Stephen_IV]

My Aswer Is This, I used your approach! Thanks!

 

[Stephen_IV]

How would I amend the code so that I can also highlight YNNNNN?

 

[My Aswer Is This]

Try this:

Sub Auto_Filter()
'Modified 9-2-17 1:35 PM  EDT
Application.ScreenUpdating = False

    With Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="Y"
        .AutoFilter Field:=2, Criteria1:="N"
        .AutoFilter Field:=3, Criteria1:="N"
        .AutoFilter Field:=4, Criteria1:="N"
        .AutoFilter Field:=5, Criteria1:="N"
        .AutoFilter Field:=6, Criteria1:="N"
        .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 3
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

You may find it easier to adjust arrPattern variable in below for any pattern you wish to highlight:

Sub Auto_Filter()
    
    Dim LR            As Long
    Dim x             As Long
    Dim arrPattern ()  As String
        
    arrPattern = Split("Y|N|N|N|N|N", "|")

    Application.ScreenUpdating = False
        
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        .Cells.Interior.ColorIndex = xlNone
        LR = .Cells(.Rows.Count, 1).End(xlUp).row
        
        With Cells(1, 1).Resize(LR, 6)
            For x = LBound(arrPattern) To UBound(arrPattern)
                .AutoFilter Field:=x + 1, Criteria1:=arrPattern(x)
            Next x
            .Offset(1).Resize(LR - 1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 4
        End With
        
        .AutoFilterMode = False
    End With
    
    Application.ScreenUpdating = True
    Erase arrPattern
       
End Sub