Possible to use formula bar to check color of cells

mrbud1972

New Member
Joined
Jul 5, 2011
Messages
3
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 5, 2011
Messages
3
Thanks for that. As I said, it wasnt really VBA I was after but appreciated anyway! - BTW,,, my Location : Preston!! Small world :cool:
 

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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 :cool:
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,055

ADVERTISEMENT

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

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,055
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.
 

Forum statistics

Threads
1,137,154
Messages
5,679,914
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top