Option Explicit
Function ConditionalColor(rg As Range, FormatType As String) As Long
'Code by Brad Yundt - July 1998
Dim cel As Range
Dim tmp As Variant
Dim boo As Boolean
Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
Dim i As Long
Set cel = rg.Cells(1, 1)
Select Case Left(LCase(FormatType), 1)
Case "f" 'Font color
ConditionalColor = cel.Font.ColorIndex
Case Else 'Interior or highlight color
ConditionalColor = cel.Interior.ColorIndex
End Select
If cel.FormatConditions.Count > 0 Then
'On Error Resume Next
With cel.FormatConditions
For i = 1 To .Count 'Loop through the three possible format conditions for each cell
frmla = .Item(i).Formula1
If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
'Conditional Formatting is interpreted relative to the active cell. _
This cause the wrong results if the formula isn't restated relative to the cell containing the _
Conditional Formatting--hence the workaround using ConvertFormula twice in a row. _
If the function were not called using a worksheet formula, you could just activate the cell instead.
frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
boo = Application.Evaluate(frmlaA1)
Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
Select Case .Item(i).Operator
Case xlEqual ' = x
frmla = cel & "=" & .Item(i).Formula1
Case xlNotEqual ' <> x
frmla = cel & "<>" & .Item(i).Formula1
Case xlBetween 'x <= cel <= y
frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
Case xlNotBetween 'x > cel or cel > y
frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
Case xlLess ' < x
frmla = cel & "<" & .Item(i).Formula1
Case xlLessEqual ' <= x
frmla = cel & "<=" & .Item(i).Formula1
Case xlGreater ' > x
frmla = cel & ">" & .Item(i).Formula1
Case xlGreaterEqual ' >= x
frmla = cel & ">=" & .Item(i).Formula1
End Select
boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
End If
If boo Then 'If this Format Condition is satisfied
On Error Resume Next
Select Case Left(LCase(FormatType), 1)
Case "f" 'Font color
tmp = .Item(i).Font.ColorIndex
Case Else 'Interior or highlight color
tmp = .Item(i).Interior.ColorIndex
End Select
If Err = 0 Then ConditionalColor = tmp
Err.Clear
On Error GoTo 0
Exit For 'Since Format Condition is satisfied, exit the inner loop
End If
Next i
End With
End If
End Function
Sub NonConditionalFormatting()
Dim cel As Range
Application.ScreenUpdating = False
'Remove conditional formatting from entire worksheet
'For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each cel In Selection 'Remove conditional formatting from selected cells
If cel.FormatConditions.Count > 0 Then
'cel.Activate 'Without this step, the "Formula Is" applies just to the first cell in the range
cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color
cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color
cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell
End If
Next cel
Application.ScreenUpdating = True
End Sub
