none
Change Conditional Formatting Color to Static RRS feed

  • Question

  • Hello,

    I have an excel WS with 20 Columns and 50,000+ rows with several conditional formatting. Is it possible to change all cells color to static?
    Any help is appreciated.

    Thanks!

    Wednesday, November 21, 2018 5:59 PM

Answers

All replies

  • Hi Hanna,

    Please try the VBA code as below:

    Option Explicit
    Sub a()
    
    Dim iconditionno As Integer
    Dim rng, rgeCell As Range
    Set rng = Range("A1:A10")
    
    For Each rgeCell In rng
    
       If rgeCell.FormatConditions.Count <> 0 Then
           iconditionno = ConditionNo(rgeCell)
           If iconditionno <> 0 Then
               rgeCell.Interior.ColorIndex = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex
               rgeCell.Font.ColorIndex = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
           End If
       End If
    Next rgeCell
    
    End Sub
    Private Function ConditionNo(ByVal rgeCell As Range) As Integer
    
    Dim iconditionscount As Integer
    Dim objFormatCondition As FormatCondition
    
        For iconditionscount = 1 To rgeCell.FormatConditions.Count
            Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
            Select Case objFormatCondition.Type
               Case xlCellValue
                   Select Case objFormatCondition.Operator
                       Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                               Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                               ConditionNo = iconditionscount
    
                       Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                               Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                               ConditionNo = iconditionscount
    
                       Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                               ConditionNo = iconditionscount
    
                       Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                               ConditionNo = iconditionscount
    
                       Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                               ConditionNo = iconditionscount
    
                       Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                               ConditionNo = iconditionscount
    
                       Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                               ConditionNo = iconditionscount
    
                       Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                               ConditionNo = iconditionscount
    
                      If ConditionNo > 0 Then Exit Function
                  End Select
    
              Case xlExpression
                If Application.Evaluate(objFormatCondition.Formula1) Then
                   ConditionNo = iconditionscount
                   Exit Function
                End If
           End Select
    
        Next iconditionscount
    End Function
    
    Private Function Compare(ByVal vValue1 As Variant, _
                             ByVal sOperator As String, _
                             ByVal vValue2 As Variant) As Boolean
    
       If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application.Evaluate(vValue1)
       If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application.Evaluate(vValue2)
    
       If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
       If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)
    
       Select Case sOperator
          Case "=": Compare = (vValue1 = vValue2)
          Case "<": Compare = (vValue1 < vValue2)
          Case "<=": Compare = (vValue1 <= vValue2)
          Case ">": Compare = (vValue1 > vValue2)
          Case ">=": Compare = (vValue1 >= vValue2)
          Case "<>": Compare = (vValue1 <> vValue2)
       End Select
    End Function

    For more information, please review the following link:

    Excel: Make conditional formatting static

    Hopefully it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 22, 2018 5:26 AM
    Moderator
  • Thank you Yuki.

    I am getting "Run-time error '13'" for the following line:

    Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)

    Thursday, November 22, 2018 12:48 PM
  • Hi Hanna,

    Sorry for the late reply.

    You can follow the steps in the following link to fix run time error '13' in Excel VBA:

    How to fix run time error '13' in Excel VBA?

    Hopefully it helps you.

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, November 27, 2018 8:52 AM
    Moderator
  • Also see http://www.eileenslounge.com/viewtopic.php?f=27&t=31288

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by E.Hanna Tuesday, November 27, 2018 3:58 PM
    Tuesday, November 27, 2018 10:15 AM
  • Thank you!
    Tuesday, November 27, 2018 3:58 PM