Return a value based upon a cell colour

New Contributor

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

13 Replies


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

They have been applied directly


You need custom functions for that.

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

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)


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

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

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...


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.

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


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

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?

2022-06-16 083028.jpg



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

Thank you a lot! I will try it!!