Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Format vs Interior ColorIndex (03)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format vs Interior ColorIndex (03)

    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

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format vs Interior ColorIndex (03)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format vs Interior ColorIndex (03)

    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?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format vs Interior ColorIndex (03)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format vs Interior ColorIndex (03)

    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

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format vs Interior ColorIndex (03)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format vs Interior ColorIndex (03)

    I repeat from higher up in this thread
    <hr>ActiveCell.Interior.ColorIndex returns the color index specified in the Pattern tab of Format | Cells... It does NOT take conditional formatting into account.<hr>
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •