Possible to use formula bar to check color of cells

[mrbud1972]

Hi, is it possible to use the formula bar to search for color of a cell instead of using the conditional formatting, or even.
So basically I want it so if a cell is a particular color I will run a formula.
I know its possible in VBA, but thats not what i'm after.
Thanks in advance,

Martin.

 

[JazzSP8]

Hi Martin - Welcome to the Forum

I don't think there's a way you can do it natively within Excel but I've got this link bookmarked with some custom colour functions you might be able to use?

http://www.cpearson.com/excel/colors.aspx

HTH

 

[mrbud1972]

Thanks for that. As I said, it wasnt really VBA I was after but appreciated anyway! - BTW,,, my Location : Preston!! Small world

 

[JazzSP8]

Yeah, clocked you didn't want a VBA solution but it's not really the same, there functions which can be used as formulas?

As there's nothing native (as far as I know) in Excel I thought it might work for you if you wanted to include them in an IF statement or something

And, yup - Small world - I work in Preston

 

[mikerickson]

You could use a defined formula
Name: colorOfCell
RefersTo: =GET.CELL(38,Sheet1!$A1)*(ROWS(Sheet1!1:65536)>0)

and a worksheet formula like =IF(colorOfCell = 3, "the cell is red", "not red")

http://www.mrexcel.com/forum/showthread.php?t=20611

Note that the *(ROWS(1:65536)>1) is there to make colorOfCell recalculate every time any cell in the sheet has its value changed. NOTE: coloring a cell will not trigger a re-calc, some cell has to have its value changed to trigger a re-calc.

 

[Jaafar Tribak]

You can use the XLM GET.CELL function to get the fill color of a cell

Add the named formula : CellColor which refers to : =GET.CELL(63,INDIRECT("RC",FALSE))

Place the =CellColor function in a cell and it will return the cell's current fill color every time the sheet calculates. No VBA involved.

One limitation is that the formula will not refresh when changing the cell's fill color untill the sheet is recalculated.

 

[Jaafar Tribak]

Same approach posted at exactly the same time - that was a funny coincidence

 

[mrbud1972]

Thats amazing!!! I knew you guys wouldnt let me down!
~Smashing~

 

[mikerickson]

Thats amazing!!! I knew you guys wouldnt let me down!

I'm not sure that we didn't let you down. Letting you continue down the path of treating a cell's color as data is letting you get into trouble, when the wisest course would be to redesign your spreadsheet now, rather than continuing to develop a bad idea.

Using color as data is not good. If the fact that a cell is green is meaningful, the obvious question is "what does green mean?"
If it distinguishes between different divisions, is green the west division or the North. (Better to put "West" or "North" in cell)
If it indicates importance, is green more or less important than blue? (Better to indicated importance with an ordered variable e.g. 1,2,3..., e.g. A,B,C...)

Color is great for highliting and using it to focus a human's attention on particular group of cells. But, asking Excel to read and interpret a cell's color is not intuitive, neither is the correspondense between the color and its meaning as clear as text or number.

As I mentioned, changing a cell's color does not trigger a re-calculation of a sheet. Thus, you have (effectivly) forced yourself to work in a Calculation=Manual environment, which can be an endless source for error.

Furthermore, color that results from Conditional Formatting is not detected by GET.CELL or even by non-amazingly-complicated VBA.


Now that I've warned you off of plans to detect and use color with formulas, now I can feel that I haven't led you down the garden path.