Search code examples
excelvbaexcel-2010

Update Cells in on Column if it Meets Criteria after Filter Another Column Using VBA

I am trying to write a code to filter data in column "A" to only "ZSFG" and "ZCNC" values. Once this criterion is met, I need to update column "C" to make sure all cells have an "X" and highlight the values that have been updated

Data:

Data

Sub Macro1()

    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$C$999999").AutoFilter Field:=1, Criteria1:=Array( _
        "ZCNC", "ZSFG"), Operator:=xlFilterValues
    ActiveSheet.Range("$A$2:$C$999999").AutoFilter Field:=3, Criteria1:="="

   
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
     Selection.SpecialCells(xlCellTypeVisible).Offset(, 5).Value = "X"
    ActiveSheet.ShowAllData
End Sub

I have tried this code, but I am not getting what I am looking for.

Note: this is a large dataset, and the number of rows is never the same.

Can someone please help me?

I tried running the code above, and a few other ones. I have been able to highlight the cells that are empty but when I run the code again, the cells that get highlighted shift. also, the cells don't get fill with an X with this code

Solution

  • Try this code:

    Sub Macro1()

Dim rg As Range
Set rg = ActiveSheet.Range("A1").CurrentRegion

With rg
    .AutoFilter Field:=1, Criteria1:=Array( _
        "ZCNC", "ZSFG"), Operator:=xlFilterValues
    .AutoFilter Field:=3, Criteria1:="="
    
    With .Columns(3).Offset(1).Resize(.Rows.Count - 1)
        With .SpecialCells(xlCellTypeVisible)
            .Value = "x"
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End With
    End With

    .Parent.ShowAllData
End With
End Sub

    First of all I wouldn't rely on selection. If your table is clean you can use CurrentRegion to set the range to work on.

    You can use SpecialCells but you have to "prepare" the base range beforehand.

    BTW: you could achieve all that with a formula and conditional formatting as well.