MS Excel: I need to create a custom formula in which if BOTH conditions are met, then the output = 1, otherwise output = 0

Question

Condition 1: Cell A1 should not be empty

Condition 2: The interior colour of cell A1 is a specific colour. In this case its specifically "light green"

If both of these conditions are met, then the out should be 1. If one or both conditions aren't met then the output should be 0.

What the end result should look like

Now so far I have come up with this IF formula: =if(or(getColor(A1)<>1234, isblank(A1)),0,1)

The 1234 is meant to represent the RGB code for a given colour. In this case I didn't know what the RGB code for light green was in Excel so I just put in 1234 for the sake of my question.

Now I know the 2nd half of this formula which starts with isblank (and continue onward) is fine. But the first half of this formula is causing the formula to break. Turns out getColor isn't really an Excel formula.

Can someone help me with setting up the condition which satisfies the condition: IF the filled color for A1 is light green, then...

Tim Williams Tim Williams · Accepted Answer · 2021-01-05T07:44:51

You can use a simple user-defined function.

In a regular VBA code module:

Function getColor(c As Range) as Long
    getColor = c.Interior.Color
End Function

Note this will not work if the coloring is due to Conditional Formatting.

MS Excel: I need to create a custom formula in which if BOTH conditions are met, then the output = 1, otherwise output = 0

1 Answers