Here Is A Function To Grab Cell (Conditional) Colors

Bump to notify people of an incorrect file upload for the initial post.

I finally had time to come up with a function that will return the color of a cell, whether the cell has conditional formatting or not.  The link below points to an Excel 2007 file that has a function in it that will determine the appropriate color to return for the cell sent into it.  I have tried to cover all of the conditional formatting options, including tracking priority and "stop if true" for cells with overlapping conditions.  I have intentially left some condtions out (like "icon sets", which don't really have a color associated with them).

Part of the code is from existing sources (here and elsewhere).  The other part is original.  I created the function in response to a user's desire to be able to color bubble chart bubbles using conditionally formatted cell colors.

The linked file includes the function "GetCellColor", which returns the color of a single cell, and "GetRangeColors", which returns a 1-D array of cell colors.  The file also includes a couple of demo routines "test" and "test2", and some supporting functions.

Enjoy!

Eric

Update:  I uploaded the wrong version of the file yesterday.  My apologies.  That version did not correctly handle end conditions for the color scale conditional formats.  The version below does.

http://boxstr.net/files/6953861_qbami/Conditional_Formatting_Test%20R2.xlsm 

I have not independently tested your code, but your sample file seems to show it works correctly. I can think of only one thing to say... WOW!!!! Excellent, excellent, excellent work!!! I knew there would be a lot of code involved, but that is ridiculous... one would have thought Microsoft should have been able to create a much simpler object model for Conditional Formatting than the one they ended up implementing. Thank you so much for doing the hard work involved... I know I will not be the only one thanking you for this.

May I make two suggestions please. First, you should enlist one of the MVPs with a well-established web site to host your solution (and then give us the link to the article) so that we can more easily point people who ask for this functionality in the future to it. Second, a possible addition to your function might be an optional argument for your code to be able to return to the programmer whether the returned color is an interior color or a Conditional Formatting color (or possibly a Conditional Formatting color overwriting an interior color)... a simple 0, 1, 2 would be sufficient I would think (although perhaps -1, 0, +1 could be used instead). While I don't have any particular problems in mind where that information would be needed, it seems like returning that information to an optional argument, just in case, would not be too hard to add to your function and it might be useful on occasion for the programmer to know where the color came from.

Again, execellent work!

Rick,

Thank you so much for your kind words.  The work I did was really an extension of the work of others (as is often the case), but it was certainly an effort to puzzle out the more capable, more complex conditional formatting in Office 2007+.

There are certainly improvements that can be made to the code, and your suggestion is a good one.  In fact, I already added it!  The latest version of the function GetColorCode returns the type (0=regular, 1=conditional, 2=combo) in an optional parameter.  The workbook linked below (also at Boxstr.net) includes that improved function, along with a subroutine and a function that allow "color math" - counting, summing, averages, etc based on fill colors and cell values.  Give it a try.

http://boxstr.net/files/7347104_azlxm/MathByColor.xlsm

I would be happy to have this file hosted on a more accessible site, if any of the Major VBA Players (MVPs) would like to include it in their collection.

Eric

I am having problems downloading the file from boxstr.  Can you post or send it another way?

Let's see if we can find a better place to host the file.  You might also try the new link I just posted - it's a different file, but with the same color function.

Eric

+1 (and more) for putting this together, this has been a long-time recurring question/request in the forum. Great work.

My first thought when I read your post was the multi-series bubble chart from Tom Hollander's blog http://blogs.msdn.com/b/tomholl/archive/2011/03/28/creating-multi-series-bubble-charts-in-excel.aspx (and other places). Being able to create (and dynamically update) bubble chart colors based on cell formating is a wonderful idea!

I haven't had time to dive into your code (I just saw this post), but I look forward to learning more!

Did you have any luck hosting the file in another location?  The links redirect to GeekWise and I would really love to see the code.

Seconded.

Eric, I wasn't aware you'd done this.  Can't wait to see it, if you can get it hosted somewhere.  Until one of the MVP's with a website picks this up, could you put it on skydrive or dropbox?

Cheers
Rich

I am having problems downloading the file from boxstr.  Can you post or send it another way?

Let's see if we can find a better place to host the file.  You might also try the new link I just posted - it's a different file, but with the same color function.

 

Eric

I haven't been on here in quite a while, so I apologize for being slow to respond.

Let's try a Skydrive link.  Please check it out and let me know if you are able to grab the file.

Eric

https://skydrive.live.com/redir?resid=C94F206ED361B07A!178&authkey=!AD96HSfTguq8yag