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

Quote:

---

Originally Posted by Rick Rothstein

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

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!!!!

Quote:

---

Originally Posted by deanmosh

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.

Quote:

---

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)

Quote:

---

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.

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)

Attachment 1287


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

Attachment 1288

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.

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

(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