• Conditional Format vs Interior ColorIndex (03)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Conditional Format vs Interior ColorIndex (03)

    Author
    Topic
    #431968

    I am looking for the reason why the conditional format (interior color: red) is different from a cell’s interior.colorindex of 3.

    How does one read the conditional cell color format with VBA?

    Thanks,
    John

    Viewing 0 reply threads
    Author
    Replies
    • #1012276

      They shouldn’t be different, unless you have changed the default color palette – check this in the Color tab of Tools | Options…

      The color index for the first condition in conditional formatting is Activecell.FormatConditions(1).Interior.ColorIndex

      • #1012278

        Hans,

        I have not changed the color palette. I have attached a sample file.

        If I run code for the active cell ie ActiveCell.Interior.ColorIndex

        I receive a -4142.

        Regards,
        John

        • #1012280

          ActiveCell.Interior.ColorIndex returns the color index specified in the Pattern tab of Format | Cells… It does NOT take conditional formatting into account. The value -4142 is xlColorIndexNone, i.e. transparent.

          The reds in the two groups of cells in your attachment are *exactly* the same on my PC. Are they different on yours?

          • #1012382

            Hans,

            They are the same on my PC. I understand what you’re saying about the transparency. How does one determine the conditional formatting of red? Each condition can be a different color.

            Regards,
            John

            • #1012384

              For example:

              Sub WhatColor()
              Dim i As Integer
              If ActiveCell.FormatConditions.Count > 0 Then
              For i = 1 To ActiveCell.FormatConditions.Count
              MsgBox “Condition ” & i & ” – color index ” & ActiveCell.FormatConditions(i).Interior.ColorIndex
              Next i
              Else
              MsgBox “No conditional formatting”
              End If
              End Sub

            • #1012387

              Hans,

              I now understand how the conditional format retains the colorindex for each of the cells in question. What appears to be a challenge for me is how Excel handles the visual display of the conditional format. In my sample file the conditional format was to fill the cell with ‘red’ if the amount in the cell is zero. I want to test the fill color, in this case I belive the code is ActiveCell.Interior.ColorIndex = 3. Unfortuantely it displays ‘red’ but the results are -4142 or transparent.

              Is it possible to test for a ‘red fill’ or am I missing the concept.

              Regards,
              John

            • #1012394

              I repeat from higher up in this thread[indent]


              ActiveCell.Interior.ColorIndex returns the color index specified in the Pattern tab of Format | Cells… It does NOT take conditional formatting into account.


              [/indent]If you want to know which color is currently displayed, you can either use the same tests as conditional formatting in your code, or use the functions from Chip Pearson’s Conditional Formatting Colors.

    Viewing 0 reply threads
    Reply To: Conditional Format vs Interior ColorIndex (03)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: