Return a value based upon a cell colour

[paul-cas1962]

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

[#2]

@paul-cas1962 

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

[#3]

They have been applied directly

[#4]

@paul-cas1962 

You need custom functions for that.

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

[#5]

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.

[#6 · Accepted solution]

@paul-cas1962 

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

[#7]

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

[#8]

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

[#9]

@Mmaayy999 

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.

[#10]

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

[#11]

@Mmaayy999 

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

[#12]

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?

[#13]

@Mmaayy999 

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

[#14]

Thank you a lot! I will try it!!