Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Return a value based upon a cell colour

Copper Contributor

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

13 Replies

@paul-cas1962 

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

They have been applied directly

@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)

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 (Copper Contributor)
Solution

@paul-cas1962 

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

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

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

@Mmaayy999 

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

 

@Mmaayy999 

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

Thank you a lot! I will try it!!
1 best response

Accepted Solutions
best response confirmed by paul-cas1962 (Copper Contributor)
Solution

@paul-cas1962 

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

View solution in original post