Results 1 to 6 of 6

Thread: Getting Colorindex for manual formatted and conditional formatted rows.

  1. #1
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016

    Getting Colorindex for manual formatted and conditional formatted rows.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    If I have a sheet where some rows were highlighted with Conditional Formatting, and some where formatted manually (using the same colour - turqoise blue colorindex=8).

    Now if I run this simple code:

    Code:
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    For i = 9 To LastRow
    Debug.Print Range("A" & i).Interior.ColorIndex
    Next i
    why does it only show the interior.colorindex of 8 for the manually coloured rows? For the conditional formatting it shows -4142 (which I think means no colour).

    The output looks like this (there should be more 8's identifying the conditionally formatted cells)

    8
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    8
    -4142
    -4142
    8
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    -4142
    Last edited by NBVC; 2015-05-01 at 02:47 PM.


  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Might this give some insight ?

  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Hi NoS, thanks. I did some more research after posting. I didn't realize that VBA doesn't recognize the conditional formatting colour, although you can use VBA to conditionally format


  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    You can use VBA to retrieve the CF- formatting:

    Code:
    Sub M_snb()
       UsedRange.Columns(1).Offset(8).Copy
    
       With CreateObject("word.document")
          .Content.PasteSpecial 1
          For Each cl In .Tables(1).Range.Cells
           MsgBox cl.Shading.BackgroundPatternColor
          Next
          .close 0
       End With
    End Sub

  5. #5
    Acolyte Andy Pope's Avatar
    Join Date
    Mar 2011
    Location
    London, England
    Posts
    41
    Articles
    0
    Excel Version
    2016
    If you have a new enough version of excel (xl2010 or newer) then you can use the DisplayFormat object.

    Code:
    for each xx in selection:?xx.interior.colorindex,xx.displayformat.interior.colorindex:next xx
    Cheers
    Andy

    www.andypope.info

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Thanks guys.


Posting Permissions

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