Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA- With Block Error during Conditional Formatting

0

I am getting a with block error when it sorts my sheet based on cell color. I can not figure out where to put the with to get to stop erroring out. Can someone please help look at my code and see whats wrong with it. I have also used macros to assist and im still getting this error. 

Sub Color_Conditions()
'
' Color_Conditions Macro
'
Sheets("Skill RAW DATA").Range("A:BZ").Columns.Select
Selection.AutoFilter
    ActiveWorkbook.Worksheets("Skill RAW DATA").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Skill RAW DATA").AutoFilter.Sort.SortFields.Add2 _
        Key:=Range("B1:B5000"), SortOn:=xlSortOnCellColor, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Skill RAW DATA").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
excelmacros-vba
MS Office Version: 365
posted by: kmowersvba (rep: 4) on Wed Feb 15, 2023 at 12:29 pm
Answer
Discuss

Answers

0
Selected Answer

Hi again Kmowersvba

You didn't attach a file to your question so I'm not clear what you're doing here but it look like you used the macro recorder.

I think the problem occurs if the macro runs normally, autofilters are set on A:BZ. If you run it again, it selects the same columns but the line:

Selection.AutoFilter

turns the filter off so the next line fails since it tries to find a sort field to clear but there's neither a filter nor a sort field.

You can add a line to check and turns filters off if necessary- see the change (/comments) in bold below:

Sub Color_Conditions()
'
' Color_Conditions Macro
'
Sheets("Skill RAW DATA").Range("A:BZ").Columns.Select

' turn Autofilter off if it's already on
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
' apply autofilter to selection
Selection.AutoFilter

(leaving rest of the code as present).

You shouldn't get the error then but need to sort out what your macro does.

Hope this helps. If so. please remember to mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Wed Feb 15, 2023 at 5:23 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login