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]

Here's a couple macros I was working on.


Main issue is that I doubt 'cell.Formula' is a way to describe satisfied Conditional Formatting


Fixed Range:

Sub ClearContents()
Dim cell As Range
For Each cell In Range("AI101:AM500")


If cell.Value = "x" And cell.Formula = "Long string of conditional formatting code here" Then
cell.Value = ""


ElseIf cell.Value = "y" And cell.Formula = "Long string of conditional formatting code here" Then
cell.Value = ""


End If
Next cell
End Sub

OR


Variable Range: (I don't think Last Row Count code here is proper)

Sub ClearContents()
Dim LastRow As Integer
Dim Row As Integer


LastRow = Range("AI101:AM" & Rows.Count).End(xlUp).Row


For Row = 101 To LastRow


If (cell.Value = "x" Or cell.Value = "y") And cell.Formula = "Long string of conditional formatting code here" Then
cell.Value = ""


End If
Next
End Sub

 

[wittonlin]

Oh crap. "There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered.""


From https://bettersolutions.com/excel/conditional-formatting/vba-code.htm


Formula1 Property


Copying and pasting a cell over a cell that uses conditional formatting wipes out the formatting rules
There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered."
When a Conditional Formatting formula uses a relative range reference, accessing the Formula1 using VBA gives you a different formula, depending on the active cell position.
Convert the formula to R1C1 notation using the active cells as the reference
Then convert that R1C1 formula back to A1 style

F1 = Range("A1").FormatConditions(1).Formula1 
F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell) 
F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Range("A1"))

 

[wittonlin]

Any ideas guys?

 

[rlv01]

Oh crap. "There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered.""

That's not really true. You can access that state via the .DisplayFormat property.

''' Test for active conditional formatting for A1:A10
Sub TestForActiveFormatCondition()
    Dim R As Range
    Dim S As String

    CreateTestFormatConditions                        'set up some format conditions

    With ActiveSheet
        For Each R In .Range("A1:A10")
            If R.DisplayFormat.Interior.Color = RGB(255, 255, 0) Then
                S = S & R.Address & vbCr
            End If
        Next R
    End With

    If S <> "" Then
        MsgBox "These cells are displaying a yellow background:" & vbCr & vbCr & S
    Else
        MsgBox "No cells are displaying a yellow background"
    End If
End Sub

''
''' This example defines conditional formatting for A1:A10 of the active sheet
''' such that the BG color will turn yellow for any non-empty cell
'''
Sub CreateTestFormatConditions()
    Dim WS As Worksheet
    Dim FormulaStr As String
    Dim MyRange As Range

    With ActiveSheet
        Set MyRange = .Range("A1:A10")
        MyRange.FormatConditions.Delete               'delete any pre-existing format conditions.
    End With

    FormulaStr = "=A1<>" & """" & """"                'define the conditional formatting forumula

    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:=FormulaStr    'add the format condition
    With MyRange.FormatConditions(1)
        With .Interior                                'configure formatting
            .PatternColorIndex = xlAutomatic
            .Color = RGB(255, 255, 0)                 'yellow
            .TintAndShade = 0
        End With
        .StopIfTrue = True
    End With
End Sub

 

[RoryA]

You should know the forum rule on cross-posting by now. Please update your thread with the relevant links. Thanks.

 

[wittonlin]

You should know the forum rule on cross-posting by now. Please update your thread with the relevant links. Thanks.

Yes sir, you are right. I forgot to add a notice here, going there. It's at Ozgrid.com

 

[wittonlin]

That's not really true. You can access that state via the .DisplayFormat property.

First, you brought up some things that makes me think this is possible now.

One thing right off that I forgot about was to """" double quote everything INSIDE the main conditional formatting string!


It's now this...

"=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"

It's not huge because ALL my multiple conditional formatting for each area is saved on another sheet and brought over in place on demand, so I'm not worried about messing up.

That said, I'm not trying to remove or add any conditional formatting code at all; so no need for FormatConditions.Delete or FormatConditions.Add code etc...

All I need to do is just recognize, on demand (running a macro) when these 2 conditions are met...

1) Conditional Formatting above IS triggered. For what it's worth, when this conditional code is triggered (the conditions I'm looking for) the cell font is bold red and cell background Color = RGB(51, 51, 0) dark green/black.

AND

2) There's either an "x" or "y" VALUE.

THEN

ClearContents of THAT cell in the range.

 

[wittonlin]

Maybe something like this...

If (cell.Value = "x" Or cell.Value = "y") And R.DisplayFormat.Interior.Color = RGB(51, 51, 0) Then
cell.Value = ""

 

[wittonlin]

.