VBA - Unable to loop through sheets to add ConditionFormats to Multiple Cells

Li, Jun 21 Reputation points
2022-10-24T08:12:06.547+00:00

When looping through sheets to add conditional formats to ONE Cell, the code runs fine.
When looping through sheets to add conditional formats to multiple Cells, an error of "-2147417848(80010108)" occurs, showing failure of clearing ColorStops.
What is the proper way to fix the code below?

Sub con_form():  
  
For Each sh In Worksheets:  
    sh.Activate  
    sh.Cells.FormatConditions.Delete  
          
    sh.Range("K24").Select  
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _  
        "=AND(K8<>""A"", NOT(ISBLANK(K24) ) )"  
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority  
    With Selection.FormatConditions(1).Interior  
        .Pattern = xlPatternLinearGradient  
        .Gradient.Degree = 90  
        .Gradient.ColorStops.clear  
    End With  
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)  
        .ThemeColor = xlThemeColorDark1  
        .TintAndShade = 0  
    End With  
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)  
        .Color = 255  
        .TintAndShade = 0  
    End With  
   Selection.FormatConditions(1).StopIfTrue = False  
  
 sh.Range("K25").Select  
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _  
        "=AND(K8=""B"", ISBLANK(K25) )"  
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority  
    With Selection.FormatConditions(1).Interior  
        .Pattern = xlPatternLinearGradient  
        .Gradient.Degree = 90  
        .Gradient.ColorStops.clear  
    End With  
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)  
        .ThemeColor = xlThemeColorDark1  
        .TintAndShade = 0  
    End With  
    With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)  
        .ThemeColor = xlThemeColorAccent2  
        .TintAndShade = 0  
    End With  
    Selection.FormatConditions(1).StopIfTrue = False  
   
Next  
End Sub  
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,720 questions
0 comments No comments
{count} votes

Accepted answer
  1. Oskar Shon 866 Reputation points MVP
    2022-11-09T11:09:01.29+00:00

    First turn or paste into your module line on the top

    Option Explicit  
    

    For future you can set it in option

    258716-xl-wymuszenie-deklaracji-zmiennych-option-explicit.png

    Then you 'll se that sh is what? - i know that you know it but good to declare it
    check if sh is visible if that is a loop
    check if sh is enable to modify (not locked)

    If sh.Visible = xlSheetVisible And sh.ProtectionMode = True Then  
    

    do not use selection - use variables to set area or with to delete select commend

      With sh.Range("K24")  
         .FormatConditions.Delete  
     '....'  
    

    record real formatting and then try to modify code to optimize it.

    Regards

    0 comments No comments

0 additional answers

Sort by: Most helpful