I would like to use Excel's conditinal formatting of a particular column based on values in two other columns.

- I have values in columns A, B, C and D.
- column A has text values (any value user enters)
- column B has a numeric value (any value user enters)
- column C has a text value selected from a set of values (data validation using drop-down):
`Start`

`Intermediate`

`End`

column D is defined as (x stands for any row number)

`=IF(Cx="End";"";Bx)`

user can as well delete the formula in column D thus making it empty

**I want values in column A displayed in red when D is empty and user didn't select End in column C.** So this only happens when a user deletes the formula in D.

So I thought of creating a conditional formatting rule that would do the trick for me. But when I try to write the condition formula it doesn't work.

I've tried specifying this formula for conditional formatting rule:

```
=NOT(OR(ISNUMBER(INDIRECT("D"&ROW()));INDIRECT("C"&ROW())="End"))
```

but it didn't work.

Are there any limitations which functions can be used in this formula, because when I use something like `OR()`

or `AND()`

it seems it simply stops working even though they're simple boolean functions that return `TRUE/FALSE`

…

## Best Answer

Based on this Superuser question I was able to solve my problem.

Here's the solution that actually works. Replace the formula with this one:

And it will work. Row number will get recalculated for each row, but columns are statically set so they'll refer to correct ones.

## Even simpler

But if we consider that

`ISBLANK()`

function returns`FALSE`

where there's a formula in a cell as well, we can simplify this even further by using this formula only: