IF statement dependent on cell fill color

[jardenp]

I would like to have a formula in, say, cell A2 that determined if cell A1 had a fill of either of two standard colors (the ones labeled "standard colors when you drop down the fill menu) and then returned a value--orange=1 and blue=2. If the cell isn't filled with one of the two colors, I want the result to be "" (blank). Is there a way to do this?

Something like =IF([fill of A1]=[orange], 1, IF([fill of A1]=[blue],2,""))

For a couple reasons, I can't use VBA/Macro for this. I know it can be done that way but I'm looking for a worksheet-formula-only solution.

Thank you!

JP in IN

 

[JLGWhiz]

I don't believe there is a way to return the fill color of a cell using formulas. I believe VBA (or other code) is the route to follow for that issue.

 

[jardenp]

I don't believe there is a way to return the fill color of a cell using formulas. I believe VBA (or other code) is the route to follow for that issue.

That's what I figured but I thought I'd run it by the experts first before trying a different approach. Thanks for the reply.

 

[Jonmo1]

There's no formula method I'm aware of.

Cell color isn't 'Data' that can be used by any formula or function.
It's strictly for your eyes only, presentation, making it look pretty.

Depending on your version of XL, the best you can do is use AUTOFILTER based on colors.
I believe that became possible in XL2007+


Now digging deeper at your actual request, how exactly are the cells 'filled' with color.
Where they filled by hand ?
Or were they filled by conditional formatting ?

If by conditional formatting, you can probably use formulas to test a cell if it meets those conditions.

If it's colored by hand, is there any recognizable pattern as to WHY each cell is colored? Why is A1 Orange, but A2 is Blue ?
If you have any kind of pattern that decides which cell gets which color, then that can be used and tested for as well.

 

[jardenp]

Jonmo1 - Filled by hand. I'm hoping to speed up something for a coworker. If I could start the process and worksheet for this task from scratch, I would. However, it's not mine to start over. Thanks.

 

[BruceJ]

Can the color of A1 be set with conditional formatting?

If so then you could a column (hidden if need be) that calculate a value based on the condition. It would return a simple value that will determine your color such a 1,2,3. Then set a conditional format for A1 based on the value in the new column.

 

[Jonmo1]

I'm hoping to speed up something for a coworker.

So this isn't something that already exists and you're trying to work with the colors.
It's something you're wanting to do, like Flagging a cell for further action by coloring it.

You'll be better served using some other method of flagging the cells for further action.
Put an x in an adjescent column for example.

 

[jardenp]

So this isn't something that already exists and you're trying to work with the colors.
It's something you're wanting to do, like Flagging a cell for further action by coloring it.

You'll be better served using some other method of flagging the cells for further action.
Put an x in an adjescent column for example.

It exists. The colors are already there, set by someone else. The current procedure is to manually enter the ones and twos based on cell color in another column. Essentially, we're entering the same data/designation twice. Too much depends on the current sheet structure (we run multiple long scripts on it) for me to add a column in an area of the sheet convenient for entry. I'll just add it to my list of things to rethink and start from scratch. Thanks.

 

[Jonmo1]

You didn't anser my other question..

Is there any pattern about 'Why' cells are colored?
Why is A1 Orange, but A2 is Blue ??

Is it one color for values > 100, another for values <20 etc?
Is it because a certain text is in the cell ?

 

[JLGWhiz]

You didn't anser my other question..

Is there any pattern about 'Why' cells are colored?
Why is A1 Orange, but A2 is Blue ??

Is it one color for values > 100, another for values <20 etc?
Is it because a certain text is in the cell ?

@Jonmo1, I think your question about the criteria (what makes the cell orange or red) is better. If the OP can define the criteria for each color, then a formula can be developed to look for that criteria and enter the onesys and twosys, etc.