Conditional formatting based of current cell AND another cell

Question

Setting up a conditional formatting based on current cell or another cell is easy. But how do you mix them together? Let's say I have the following table:

| id                                   | red | yellow | green |
|--------------------------------------|-----|--------|-------|
| e6282843-efc0-44f6-8989-028153adc317 | yes | yes    | yes   |
| 014c7590-5c3f-4260-b251-5098dd825688 |     |        | yes   |
| 6a037de4-0dc6-4e67-966b-7d6187b9d93b | yes |        |       |

I wanna highlight any cell in column red, yellow and green which is empty AND has an id on the id column.

Answer 1

Pretending that your data starts with id in column A, then B through D, put the conditional formatting range to: 'C3:E' and then choose your argument as custom formula with this:

=And(or(isblank(B:B),isblank(C:C),isblank(D:D)),istext(A:A))

Answer 2

Actually you have to translate your liking to formula:
"I wanna highlight any cell in column red, yellow and green which is empty AND has an id on the id column"
So it's or (red,yellow,green) and (id) *logic

You can try building the formula in a cell and see if it returns true when all conditions are met and false when they aren't

Say the columns are A,B,C & D

=and(A2<>"",or(B2="yes",C2="yes",D2="yes"))

This works like this: if ID is not empty (<>"" different than nothing) and... whether red=yes or yellow=yes or green=yes, the argument is TRUE, hence the format would be applied.

You can try the formula in an empty cell and see it work, if it returns TRUE and you can doublecheck it, then you can paste that in the custom formula.