Excel: Can I create a Formula based on the Color of a Cell?-CodePudding

I'm a beginner and trying to create a formula that modifies the contents of Cells of Column J based on the color of the cells in correspondent rows, I mean:

If Cell F7 is yellow then display 0 in Cell J7.

If Cell G7 is yellow then display 1 in Cell J7.

If Cell H7 is yellow then display 2 in Cell J7.

This is conditional based on the cell fill color.

I've already tried to use a some a new Name Manager =GET.CELL(6,Sheet1!F7) and then the formula =IF(CellColor=6;"1";"") -> the false value is working however the true one is not.

I also tried to write some Macros "call with" but its still not working

thanks everyone for your support.

CodePudding user response：

You cannot do this without using VBA. It is possible to create a UDF which uses Cell.Interior.Color, however, it will not recalculate upon changing colours.

I would strongly suggest finding another way to do what you want if at all possible.

CodePudding user response：

Try this macro, then run it against the filled cells. It will create a numerical value in the column to the left which you could then hide at the end of the macro.

Sub ListColors()

For Each c In Selection 

    c.Offset(0, 1).Value = c.Interior.Color 

Next c 

End Sub

You could then run any formulas against that numerical value.

For full disclosure - found this info here