Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Get Displayed Cell Color (whether from Conditional Formatting or not)

  1. #11
    Junior Member
    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    VB does does not provide direct access to the color displayed in a cell.
    As of Excel 2010, I believe this is no longer correct - you can use the Range.DisplayFormat property to access the actual displayed .Interior.Color or .Interior.ColorIndex, including conditional formatting, and including the newer conditional formatting not covered by Rick's code (e.g. color scales). However, it still does not function correctly in a UDF, and obviously Rick's code is backwards-compatible and offers additional utility. I only figured this out myself when I needed to put something together for someone who loves color scales.

    Microsoft has not done a great job of publicizing this, to put it lightly.


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-24-2023 at 02:49 PM.

  2. #12
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Gembree--

    Thanks so much for replying! I figured I would never get an answer. I've been copy and pasting my formatting to word and copying them back in, but I really hope i can get the Function from your link to work.

    Hoping you could answer a couple of quick questions for since this is on your radar..

    1. For Rick's function, and other functions in general, i can't: When someone deems a function as NOT a UDF, then what is it? What i mean is, if it's not a UDF then how is the function called?

    2. For the function, and other functions in general, is this the correct process to get it the work correctly? (Sorry, I've really tried to find a straight forward answer to this but haven't been able to get a clear response):

    1. Copy/paste the code below into a "New Module" via VBA editor

    Code:
    Function getColorIndex()   
    getColorIndex = ActiveCell.Interior.ColorIndex
    End Function
    2. Return to relevant workbook/spreadsheet, and for all cells with conditional formatting, type: "=getColorIndex()"? OR do I need to fill in the values with the ()? If so, what would those values be?

    If that's the correct way to do this then can I just copy and that cell to all relevant cells and it should spit out the actual fill color, which I can then copy and paste to other excel docs?

    Sorry for the really annoyingly basic questions. I'm by no means an expert at any of this.. just trying to make better use of my time.

    Thanks in advance for any help!!!!

  3. #13
    Junior Member
    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by deanmosh View Post
    1. For Rick's function, and other functions in general, i can't: When someone deems a function as NOT a UDF, then what is it? What i mean is, if it's not a UDF then how is the function called?
    These are "Macros", called from View Macros (Alt + F8). Rather than returning a value to a cell like a normal Excel function, they typically directly edit the spreadsheet.

    2. For the function, and other functions in general, is this the correct process to get it the work correctly?
    Close, but two things:

    1) As noted, reading conditional formatting doesn't mesh well with UDFs. For reading conditional formatting specifically, you really need to run a basic macro, like this one:

    Code:
    Sub GetTheColors()
    For i = 1 to 100
         For j = 1 to 10
              Cells(i,j+10) = Cells(i,j).FormatConditions.Interior.ColorIndex
         Next j
    Next i
    End Sub
    This writes the color indices of A1:J100 into K1:T100 when you run it.

    2) Using ActiveCell specifically in UDFs isn't a good idea, because ActiveCell refers to where the cursor is, not where the formula is. You want to pass the range in question as an argument to the function:

    Code:
    Function getColorIndex(TheColoredCell)   
    getColorIndex = TheColoredCell.Interior.ColorIndex
    End Function
    And then call the function as =getColorIndex(C37)

    If that's the correct way to do this then can I just copy and that cell to all relevant cells and it should spit out the actual fill color, which I can then copy and paste to other excel docs?
    Yes. You may need to use a "hard" recalculate [Ctrl+Alt+F9] to get it to update with any changes you've made to the color for arcane Excel reasons, though.

  4. #14
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Hi Again Gembree--

    and again thanks for the detailed help. I feel like im almost there..
    Followed your directions to try and return the actual colors of my conditional formatting, but came back with some errors.. and i was hoping you might be able to lend some advice again..

    I'll try and outline the issues as clearly as possible for you:

    (1.) As a complete novice, i feel that my mistake must be how i entered the Sub you provided


    Code:
    Sub GetTheColors()
    For i = 1 to 100
         For j = 1 to 10
              Cells(i,j+10) = Cells(i,j).FormatConditions.Interior.ColorIndex
         Next j
    Next i
    End Sub
    You indicated that this will return the real colors for cells A-J100. And in an attempt to tailor the code for my particular workbook i came up with this (i included a screencap of what I working off of) :

    Code:
    Sub GetTheColors()
    For ad = 1 To 1200
         For ab = 1 To 10
              Cells(ad, ab + 10) = Cells(ad, ab).FormatConditions.Interior.ColorIndex
         Next ab
    Next ad
    End Sub
    I used "AB" because even though the cells aren't formatted, I would prefer that actual colors be written after column AB (the image below only shows where the conditional formatting was applied)

    Capture.jpg


    When I try to run the Sub above from the macros within excel, it comes back with this error:

    Capture2.jpg

    With this line highlighted after debugging: " Cells(ad, ab + 10) = Cells(ad, ab).FormatConditions.Interior.ColorIndex"

    *Issue #1 might be solved after you take a look at my 2nd question, but i can't be sure.

    (2.) Is the Fucntion code you provided (below) intended to be copy pasted within the same module as SubGetTheColors?:

    Code:
    Function getColorIndex(TheColoredCell)   
    getColorIndex = TheColoredCell.Interior.ColorIndex
    End Function
    If i assumed correctly in (2.), then should the resulting module contain both of the aforementioned codes? Also, just wondering if the code above to 'pass the range in question as an argument to the function,' should "(TheColoredCell)" actually be "(GetTheColors)"?

    I'm just assuming that the Function should be calling the Sub...

    AGAiN thanks so much for any help you can give.

  5. #15
    Junior Member
    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    Nope - that one is my own dumb mistake, Dean. I apologize. The right property is DisplayFormat, not FormatConditions.

    In order to change the parameters, you want to change the numbers, not the letters.

    1 to 1200 is the rows to check.
    1 to 10 is the columns to check (i.e. A to J).
    Cells(i+0,j+10) is where to write the output. This writes each ColorIndex 0 rows and 10 columns to the right.

    Code:
    Sub GetTheColors()
    
    For i = 1 To 1200
         For j = 1 To 10
              Cells(i, j + 10) = Cells(i, j).DisplayFormat.Interior.ColorIndex
         Next j
    Next i
    
    End Sub
    If you want to write A1:AA1200 to AB1:BB1200, use
    Code:
    For j = 1 To 27
         Cells(i, j + 27) = Cells(i, j).DisplayFormat.Interior.ColorIndex
    Next j

    Quote Originally Posted by deanmosh View Post
    (2.) Is the Fucntion code you provided (below) intended to be copy pasted within the same module as SubGetTheColors?:

    Code:
    Function getColorIndex(TheColoredCell)   
    getColorIndex = TheColoredCell.Interior.ColorIndex
    End Function
    Doesn't matter. This function is just an example of how to write a UDF in general and has no connection with the Sub I posted (because, as discussed, a UDF can't use the DisplayFormat property due to Excel limitations). They can't pull conditionally formatted color, only normal color.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
    https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
    https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
    https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378
    https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-01-2024 at 02:16 PM.

Similar Threads

  1. Conditional formatting
    By mahmoud-lee in forum Excel Help
    Replies: 9
    Last Post: 05-30-2013, 03:00 PM
  2. Conditional Formatting using formula.
    By Ryan_Bernal in forum Excel Help
    Replies: 2
    Last Post: 02-18-2013, 11:33 PM
  3. Conditional Formatting - If/And Formula
    By Katrina in forum Excel Help
    Replies: 4
    Last Post: 11-23-2012, 12:45 AM
  4. Replies: 2
    Last Post: 09-16-2012, 02:28 AM
  5. Replies: 4
    Last Post: 07-27-2012, 08:43 AM

Tags for this Thread

Posting Permissions

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