Livin404
Well-known Member
- Joined
- Jan 7, 2019
- Messages
- 743
- Office Version
- 365
- 2019
- Platform
- Windows
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.
VBA Code:
Sub DEPEML_CatIV()
Range("C2").Select
ActiveCell.FormulaR1C1 = "E"
Range("H2").Select
ActiveCell.FormulaR1C1 = "USA"
End Sub
VBA Code:
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