• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create a macro to reset a Conditional Format

Eloise T

Active Member
I am trying to create a macro to reset a Conditional Format that looks for "OOW" in Column A and upon finding that partial string sets the font and background so it stands out. I believe the problem lies in the 7th line of the VBA code: Formula1:="=OR($A3=""OOW "")"

The Conditional Format is correctly set up as follows, but the VBA macro does not conform to the correct CF set up. Any help to adjust the VBA macro code would be appreciated....and Thank you in advance.

60659

Code:
Sub Conditional_Format_Reset()
    Dim Ws As Worksheet
    For Each Ws In ThisWorkbook.Worksheets
            If Ws.Cells(Rows.Count, "A").End(xlUp).Row > 2 Then
                With Ws.Range("A3:A7503")
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($A3=""OOW "")"
                    .FormatConditions(.FormatConditions.Count).Font.Bold = True             'Bold font
                    .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 7        'Magenta font
                    .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 4    'Green background
                    .FormatConditions(.FormatConditions.Count).StopIfTrue = False
                End With
            End If    'Concludes: If Ws.Cells(Rows.Count, "A").End(xlUp).Row > 2 Then
    Next    'Concludes: For Each ws In ThisWorkbook.Worksheets
    Beep
End Sub
 

Attachments

  • 1560402838902.png
    1560402838902.png
    42.6 KB · Views: 1
That code is not the same as the condition in your picture. You want something like:

Code:
.FormatConditions.Add Type:=xlTextString, String:="OOW", TextOperator:=xlContains

There's no point to an OR formula that just compares one cell to one value.
 
That code is not the same as the condition in your picture. You want something like:

Code:
.FormatConditions.Add Type:=xlTextString, String:="OOW", TextOperator:=xlContains

There's no point to an OR formula that just compares one cell to one value.
Your "fix" worked flawlessly. Thank you very much!
 
Back
Top