Apply Conditional Formatting to Range but Only Format Cell Where Condition Is True

Question

I have a sheet that looks like this:

The coloured cells need to be a specific colour based on their value. I am currently using conditional formatting to achieve this, but I am only able to get it to apply to a single column instead of the entire range A:G. For example, here’s my formula for column A for values that should be coloured light blue:

=OR($A1="CA515",$A1="CA525")

And applies to:

=$A:$A

Using the above formula, if any of the cells in column A contain the value CA515 or CA525, the cell alone is coloured light blue. Is there any way to use a single conditional formatting formula to make it possible that if any of the cells in the range A:G contain the value CA515 or CA525 that only that cell alone is coloured light blue? Or do I have to apply the formula to every column individually, or possibly even resort to VBA?

Thank you in advance!

Answer 1

You can avoid using VBA here*... Remove the dollar signs in your conditional statement, it should be

=OR(A1="CA515", A1="CA525")

The dollar signs specify whether the reference is relative or absolute. To visualise how this works, try typing these formulas into a cell and dragging the corner of the cell down to autofill:

=A1  'Autofilling this down will give =A2, =A3, ...
=$A1 'Autofilling this down will give =$A1, =$A1, ...

So by removing $, your format condition should be spread across the range. To set the range, change "applies to" to $A:$G.

---

Note: many conditional formats over a large range like this could severely impact the speed of your document. Consider at least limiting the number of different formats, or the number of rows it's checking.

*Although the above method does avoid VBA, it might be quicker to write your own formatting routine in VBA, since it wouldn't have to get checked so frequently and it would be unaffected by moving around of ranges which messes with conditional formats.