Note: Also **here**.

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.

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

Display More

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

Display More