ClearContents Macro based on cell text value AND satisfy conditional formatting FORMULA

[wittonlin]

I have extensive conditional formatting for an area on a (sheet) named "Data".


I need to learn "how to", leave the conditional formatting alone, and on-demand...
run a macro to ClearContents of ANY specific cells within the Range("AI101:AM" & lastRow)
BUT ONLY when TWO conditions are met...


1) Cell must contain an "x" or a "y" text value


2) It also must satisfy the following conditional formatting FORMULA


"=IFERROR(INDEX('G2-7'!$BC:$BC,MATCH("*"&$S101&"*",'G2-7'!$BD:$BD,0),1),IF(INDEX('G2-7'!$BB:$BD,MATCH($H101,'G2-7'!$BB:$BB,0),3)="",INDEX('G2-7'!$BB:$BD,MATCH($H101,'G2-7'!$BB:$BB,0),2),INDEX('G2-7'!$BB$1:$BP$20,MATCH($H101,'G2-7'!$BP:$BP,0),2)))<0"


I might have been able to get this done if Excel would recognize conditional formatting within formulas and macros (i.e. If cell = whatever background color)


Since it doesn't, one of the conditions must satisfy the conditional formatting formula that CAUSES the cell background color to change.

 

[wittonlin]

DisplayFormat.Interior.Color was the KEY, thank you rlv01!


It's clunky and takes way longer than it should but it works. Peace!

Sub ActiveFormatConditionThenRemoveContents()
    Dim cell As Range
    Dim lastRow As Long


    With ActiveSheet
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row


        For Each cell In .Range("AI101:AM" & lastRow)
            If (cell.DisplayFormat.Interior.Color = RGB(51, 51, 0) Or cell.DisplayFormat.Interior.Color = RGB(255, 255, 204)) And cell.value = "x" Then
               cell.value = ""
            End If
        Next cell
    End With


End Sub