I have generated a 50 custom color gradient in Excel 2007. Most of these colors are not preset in the color palette. Is there a vba function to get the hex color code for an individual cell filled with a custom color? Thank you in advance for your help.
Last edited by g.torrez; 07-14-2011 at 10:08 PM.
See if this sample workbook offers you any ideas.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
I was able to use that sheet to generate the rgb values separated by commas for the colors. Could somebody help me write a function to convert these numbers to hex. For example, 255, 0, 0 to FF0000.
Last edited by g.torrez; 07-14-2011 at 11:02 PM.
Hi G.Torrez,
The following macro will tell you the Long, Hex and RGB values for colors in A1:A50. It will put the corresponding values in columns B, C and D, respectively.
Please Login or Register to view this content.
The macro is generating the hex values in reverse i.e. 22DE92 instead of 92DE22. I am also have a problem with the cells in which the value begins with '0'. Instead of 0000FF, it displays FF. I have attached the file if you would like to take a look. Thank you for your assistance.
Ok, how about this code:
Please Login or Register to view this content.
The 0's now appear, but the hex code is still reversed i.e. 49B715 instead of 15B749. Any thoughts?
Should fix that.Please Login or Register to view this content.
Perfect! Thank you.
Ok. There is one issue left. With rgb value 153, 224, 31, the corresponding hex code should be 99E020. The macro produces 9.90E+21 thinking this is scientific format. Can this be fixed?
Have a look at this workbook.
Run the macro "TestColourCellFromHex" to colour fill Column E
Copy and paste a coloured cell to a cell in Range L2:L6 the formula in Range M2:M6 will return the hex code and the adjacent cell colour will update to suit.
Type a hex number in M10 down the adjacent cell in Column N will change accordingly.
If a scientific number is "detected" precede the hex code with an apostrophe to force a string.
This is not a problem with the function HexCode() it always returns a string.
Hope this helps.
Sorry I missed your posted workbook, it still might be useful to look at the workbook I last posted (Post #11).
I have added this function to your workbook
Use as a UDFPlease Login or Register to view this content.
In E1
Drag/Fill DownPlease Login or Register to view this content.
Hope this helps.
[EDIT]
None of this will work if the colours are generated by conditional formatting, these colours are only a mask, all of the code you have been given will return the underlying cell colours (the actual cell fill colour).
A handy way to get C/F colours is to copy the range to Word and then back to Excel.
The code for doing this without using Word is horrific, at best
Last edited by Marcol; 07-15-2011 at 06:15 AM. Reason: Added note on C/F colours.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks