Conditional formatting won't work if the content of the cell is a formula

Question

I've applied a custom formula conditional formatting that highlights the cell in column C if it matches with column K.

The formula I used is =MATCH(C3,$K$3:$K$988,0). However, it does not highlight if the content of the cell is a formula (the formula on the cell is =if(isblank(A47),"",C46+1)).here is a screenshot of the sheet with the formula .

I tried typing the number manually, and it works, but won't work if it is a formula. If I type it manually, the format should be PLAIN TEXT so the conditional formatting will work. I tried to put in it AUTOMATIC, it does not work. Here is a screenshot of the sheet if I type the number manually .

I'm not sure why this is happening because it works well in other sheets with the same formula. Please help me. Thank you!

Answer 1

Adding one to C46 produces a number. If the values in column K are formatted as text it won't match with them. If you want to keep the numbers in column K formatted as text, you need to convert the number you're looking up to text before doing the lookup. You can use

MATCH(text(C3,"@"),$K$3:$K$988,0) 

or just

MATCH(C3&"",$K$3:$K$988,0)