- Jan 7, 2019
- Office Version
Greetings, I am using an advance filtering VBA process where the results are immediately pasted to another worksheet. I have a series of these events happening I would like like the highlight to appear as I go rather than try to highlight these cells before unattended highlights occur if I try and do this all at once after the mass filtering is complete. I attached two macros, the first one is where the highlight in yellow would like to occur. The second macro is there for context. The macro that occurs after the second macro is one that clears the first macro and sets up the next. In the example below I would only want E in C2 and USA in H2 to be highlighted. Those letters in those columns may appear elsewhere outside that particular group on the worksheet, but in that case I wouldn't need them highlighted since the advance filter would be looking for something else.
Sub DEPEML_CatIV() Range("C2").Select ActiveCell.FormulaR1C1 = "E" Range("H2").Select ActiveCell.FormulaR1C1 = "USA" End Sub
Sub DEPEML_CatIV_Filter() Dim rgData As Range, rgCriteria As Range, rgOutput As Range Dim sh As Worksheet Dim LstRw As Long Set rgData = ThisWorkbook.Worksheets("Database").Range("A6").CurrentRegion Set rgCriteria = ThisWorkbook.Worksheets("Database").Range("A1").CurrentRegion Set rgOutput = ThisWorkbook.Worksheets("Deficiencies").Range("A1") Set sh = Sheets("Deficiencies") With sh LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row + 2 Set rgOutput = .Range("A" & LstRw) rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput End With End Sub