I have the following VLOOKUP formula for a Conditional Formatting rule:


This works fine and does what I want, which is this: If column C (3rd column) in the lookup table contains any value (i.e. <>0), apply some formatting to the current cell.

What I don't understand is the A1 value used as the first argument in the formula. When I entered the above rule using Conditional Formatting -> New rule -> Use a formula to determine which cells to format, I specified the entire worksheet of Sheet2. So when I enter a value in any cell in Sheet2, the formula is applied and really works beautifully.

But why does it work when the value to look up is specified as A1?

I am using Excel 2016 on Windows 7 x86.


Conditional formatting formula is always applied at cell level.

If you go to another cell in the range with the conditional formatting, you'll probably see different references.

  • I don't understand what you mean by "seeing different references in another cell". The formula is not in a specific cell. It is contained inside the rule I created. – Sabuncu Jan 27 '16 at 15:05

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.