Conditional Formatting after VBA has ran not working

[Claire Jackson]

Hi, I have a very simple spreadsheet that filters and sorts via VBA but the conditional formatting isn't correct once it's been sorted so I'm thinking the best way to fix it is to use VBA to conditionally format the data after the other VBA codes have been ran. Basically the data is filtered on row 3 and the data is in the range A3:Y1500. I need it to format the row (A:Y) orange if the data in column M on each row is greater than zero. Can anyone help?

The code I'm using is as follows:

Private Sub Worksheet_Activate()

Sheet4.Range("A3:BA5000").AutoFilter Field:=2
Sheet4.Range("A3:BA5000").AutoFilter Field:=7

With Range("A3:BA5000")
.AutoFilter Field:=2, Criteria1:="*Middlesbrough*"
.AutoFilter Field:=7, Criteria1:="*Submit Costs*"
End With

With Range("A3:BA5000").CurrentRegion
.Sort .Range("J3"), xlAscending, Header:=xlYes
End With

End Sub

 

[Jeffrey Mahoney]

Hi Claire,

I have macros that autofilter data all the time. The CF seem to work just fine after the filtering and sorting. Can you provide the formula you are using for the CF.

 

[Claire Jackson]

Hi, the conditional format is based on another tab which populates the data from column BE on the data tab to column M

I've uploaded a pic of the formula.

 

[Jeffrey Mahoney]

Well if you want the CF to work properly you should probably make the formula change to : =$M3>0

And the range should be: $A$3:$Y$1500

I think if you make these changes, then you wouldn't have to add any code to change the CF each time you autofilter or sort. The reason is that your rows on the other sheet are not identical to the rows on your current sheet.