Sep 11 2018 11:52 AM
I am trying to create a conditional formatting rule which would highlight the cell in column P if there is a value in column K and Column P="No". The formula I used for the rule is: =AND(NOT(ISBLANK(K2))),P2="No"). Why is that not working? Is there a better formula?
Sep 11 2018 01:56 PM
Hello,
looks like you are closing the bracket for the AND too early, so the formula returns an error.
Try the formula in a worksheet cell. If it returns "TRUE", then copy it into the conditional formatting dialog.
Here is the corrected formula:
=AND(NOT(ISBLANK(K2)),P2="No")
Sep 11 2018 02:49 PM
It is highlighting the cell about it not the actual cell. This is so strange! I have never seen something like this happen. I am usually so good with formulas and conditional formatting
Sep 11 2018 05:06 PM
Hello again.
The formula uses relative row references. They are relative to the position of the active cell when the format was defined. Looks like the active cell may have been not in row 2 if it is highlighting a cell in a row above. Fix it this way:
- Delete the wrong formatting rule and start a new one.
- Make sure to select cell P2 and use the formula from above.
- Or if you select cell P3, you need to adjust the formula to look at the cells in row 3, not row 2.