Now many of you think what is use of finding color of cell. How that will be helpful. Let me start you explaining use of finding color of cell. Like MS Office 2007 you can filter using color. However, same is not available in previous version of MS Excel. So, you can find the color of cell as numeric value and using filter on this column to filter all the values having same format. Isn’t it a good idea?
You just need one line of Code to get the color of cell. So, you can write that as function your MS Excel workbook or you can write a add-in and save on your computer. So, you can use this formula across all MS Excel workbook. I will provide another post on creating add-ins. Here, we will use the function to find color. Also, each color will be returned as number which can be used to filter
Mention below is piece of code to find the number for color used in cell.
Function findcolor(target As Range) As Integer
findcolor = target.Interior.ColorIndex
End Function
Enclosed screenshot is below show how to use the findcolor formula on worksheet after you include in MS Excel workbook.
Click on image to view it large
If you still have doubts, I have uploaded the file which you can download. If you like the above post, please post your comments.
Click here to download MS Excel find color Example
We assure you knowledge, not SPAM!
2 comments:
This is great! but some how if i have cells those are conditionally formatted using VBA macro or some conditional formatting formulas, this one is not working! any any on how to get this thing work for those cases?
Conditional formatting does not provide colorindex to cell hence this may not work. Shall Update code to handle that as well. Keep watching..!!!
Post a Comment