Snasui Excel Forum

Code: Select all

Private Sub btcheck1_Click()
    ActiveSheet.Range("$A$1:$D$100").AutoFilter Field:=4, Criteria1:=">35000", _
        Operator:=xlAnd
        Interior.Color = vbYellow
End Sub

Private Sub btcheck2_Click()
    ActiveSheet.Range("$A$1:$D$100").AutoFilter Field:=4, Criteria1:="<35000", _
        Operator:=xlOr, Criteria2:=" "
        Interior.Color = vbYellow
End Sub

Code: Select all

Private Sub btcheck1_Click()
Dim lr As Long
    lr = Range("d" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:$D$100").AutoFilter Field:=4, Criteria1:=">35000", _
        Operator:=xlAnd
    Range("d2:d" & lr).SpecialCells(xlCellTypeVisible).Interior.Color = vbYellow
End Sub
Private Sub btcheck2_Click()
Dim lr As Long
    lr = Range("d" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:$D$100").AutoFilter Field:=4, Criteria1:="<35000", _
        Operator:=xlOr, Criteria2:=" "
    Range("d2:d" & lr).SpecialCells(xlCellTypeVisible).Interior.Color = vbYellow
End Sub