How to identify background color of a cell with a formula

[kaushik03]

Hi All,


Say I have 100 cells (A1:A100) which are filled with some data. Some of these cells are filled with color (it could be any color). I want to know which are the cells filled with color and which color/colorindex it is?


I am looking for some formula based approach(no UDF).


Looking forward to your help.


Kaushik

 

[Luke M]

Afraid this is impossible without using VB. Native XL simple has no way to detecting cell color. The other possible tricky thing is if we need to determine if the cell color is from cell formatting or from conditional formatting.


Sorry I couldn't provide better news. =(


If there's some logic as to why some cells are colored, perhaps we can use that to help us out?

 

[Hui]

Kaushik


I hate to be the bearer of bad news, but what you want to do cannot be done without using a UDF


The UDF is quite simple and safe

 

[kaushik03]

I have now no doubts as NINJAs have confirmed it is not possible by formula.


Here I have come up with a UDF as you ahve suggested:

[pre]

Function GetColorIndex(Cell As Range)

GetColorIndex = Cell.Interior.ColorIndex

End Function

[/pre]

Kaushik

 

[Luke M]

Short and sweet, very nice. Sorry we couldn't come up with a preferred solution. =/

 

[kaushik03]

It's absolutely OK Luke...not a problem....


And please do not say sorry because I know, if it would have been possible in reality I would have got the answer from you....because you are 'amazing' in excel...


Actually I also tried a lot, before I post here, to build a formula to get the job done but did not get any success. As I have been learning lot of amazing things from this forum, I just wanted to check with you guys if it is at all possible with a formula.


Thank you for your suggestion and confirmation, otherwise, I would still have been wrestling with this problem in order to get a formula only....


Regards,

Kaushik

 

[Debraj]

Hi Luke,


Can you please give us a lesson on

=CELL("color",A1")

. My knowledge is very much little in this formula


Regards,

Deb

 

[bobhc]

Good morning Debraj Roy


You may find this of help


http://www.techonthenet.com/excel/formulas/cell.php

 

[Krishnakumar]

Hi


If your data in A2 on Sheet1


Select A2, Hit Ctrl + F3 > New >


Name: CELLCOLOR

Refers to: =GET.CELL(63,Sheet1!$A2)


OK.


Now in B2


=CELLCOLOR


would give you the color index of A2


Kris

 

[Krishnakumar]

For more Macro functions : http://www.excelfox.com/forum/f13/excel-macro-functions-get-cell-75/


Kris

 

[Hui]

The Get.Cell function is an Old Excel 4 Macro function that, although has lots of useful uses, is not documented or guaranteed of being supported in future versions of Excel.