SOLVED

New Contributor

# Return a value based upon a cell colour

I need to assign three different values based upon three different cell colours

13 Replies

# Re: Return a value based upon a cell colour

Have the cell colours been applied directly, or are they the result of conditional formatting?

# Re: Return a value based upon a cell colour

They have been applied directly

# Re: Return a value based upon a cell colour

You need custom functions for that.

You can either copy them from Color Functions In Excel or use ASAP Utillities (free for personal use)

# Re: Return a value based upon a cell colour

Is there a function in ASAP Utilities that will enable me to return a value based upon the colour of another cell?  I can't find such  function.

best response confirmed by paul-cas1962 (New Contributor)
Solution

# Re: Return a value based upon a cell colour

The ASAPCELLCOLORINDEX function returns the fill color index of a cell. You can use this in an IFS or SWITCH formula.

# Re: Return a value based upon a cell colour

@Hans Vogelaar  That's perfect and does exactly what I needed.  Thank you so much!

# Re: Return a value based upon a cell colour

Hi @Hans Vogelaar , is there any functions work for the color define by conditional format? I want to return header for the cell first filled with red in a row, but solution I find only work for the color defined directly, not work for conditional format... thanks in advance...

# Re: Return a value based upon a cell colour

The problem is that the actual formatting applied by conditional formatting is not available in a cell formula.

Instead of referring to the color, you'll have to use the conditions behind the conditional formatting rules. For example, if a cell is colored red if its value is greater than 100, your formula would have to search for values greater than 100.

# Re: Return a value based upon a cell colour

Thank you a lot, but the condition of the format is dynamic, which sums different range based on the cell index..

# Re: Return a value based upon a cell colour

That will make it complicated. I cannot offer a suggestion without seeing (a copy of) the workbook...

# Re: Return a value based upon a cell colour

Hi @Hans Vogelaar, below is my conditional format details, to sum the cells value before current cell and check if more than the cell value in I column of this row...maybe some suggestions?

# Re: Return a value based upon a cell colour

See the attached demo workbook. You'll have to allow macros.

# Re: Return a value based upon a cell colour

Thank you a lot! I will try it!!