Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping colorindex value of CFormatted cell (2000)

    I have been trying to run some VBA code using the colorindex value of conditionally formatted cells to return row numbers and other information about the location of that cell. For the past hour I have been trying to figure out why my code will not work, only to discover that conditional formatting does not change the .interior.colorindex value of the cell. All cells which change interior color as a result of conditional formatting show a color index value of -4142. Has anyone else had this problem?

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping colorindex value of CFormatted cell (2000)

    I think you are confusing Selection.Interior.ColorIndex with Selection.FormatConditions(n).Interior.ColorIndex - have a look at the FormatConditions object in the vba help and see if that's what you're after.

    Brooke

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping colorindex value of CFormatted cell (2000)

    I think I see some of what you're saying Brooke. I am still puzzled as to why the following will not work:

    <pre>Public Sub VarList()
    Dim ST As Long
    'ThisWorkbook.Sheets("Variance List").Visible = True
    'ThisWorkbook.Sheets("Variance List").Activate
    For Each cell In ThisWorkbook.Sheets("Deposit Rec").Range("e4:e38")
    With cell
    If .Interior.ColorIndex <> xlColorIndexNone Then
    ST = cell.Value
    PrintVar (ST)
    End If
    End With
    Next
    End Sub</pre>


    If conditional formatting actually changes the interior color of the cell, a person would think that Selection.Interior.ColorIndex would return the proper value. I am trying to return the colorindex of the cell after it has been changed via conditional formatting.

    If I put .ConditionalFormats(1).Interior.ColorIndex, this would return the colorindex of the format should the condition apply. Y? N?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Trapping colorindex value of CFormatted cell (2000)

    Mike,

    I think you best approach would be to test for the condition that gives rise to the color being applied, for example if cells < 100 are colored red, and you want to do something with th ered cells then check for values less than 100 etc. The actual colorindex of a cell remains the same regardless of conditional formatting.

    Using .ConditionalFormats(1).Interior.ColorIndex will return the actual color index that applies to condition 1, whether ithe condition is met or not.

    Andrew

Posting Permissions

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