Excel Conditional Formatting and dragging to neighbouring cells

Question

I have a condition set up =IF(C2<42,C4="") the background color will turn red if met. When dragging the crosshair (at the bottom left of the cell) to neighbouring cells, the formula stays the same.

I need the formula to then change to =IF(D2<42,D4="") and so on 300+ times, Is there any way to refer to the current column i.e =IF(thiscolumn-row2 < 42, thiscolum-row4 = "")

Solution 1

Excel sometimes by default puts dollar signs in front of the cell/row labels (ie $C$4 instead of C4 .... the dollar signs tell excel not to change the formula with each row but to lock in the original values. Does your rule in conditional formatting show dollar signs? That may be your problem. Get rid of the dollar signs and what you need should work.

Solution 2

You don't use If in Conditional Formatting formulas. The formula itself defines the condition that you are looking for, so the if is already implied. So your formula should simply be something like:

=C2<42

I'm confused about the 2nd part of your formula, C4="". Is that supposed to be a 2nd condition? If so, use an AND statement:

=AND(C2<42,C4="")

If you are using Excel 2007 or 2010 another source of potential confusion is that references don't change in the Conditional Formatting formula box when you drag them around, even if they are relative.

Comments

[Admin]

I'm using 2010, Sorry i was meant to put AND at first, this works for the column C but when i drag it to column D the formula is still =AND(C2<42,C4="") whereas i need it to be =AND(D2<42,D4="") then =AND(E2<42,E4="") etc. I have 500+ columns and was looking for a quick solution than setting up 500+ rules

[Admin]

I changed the $ and still it doesn't work, I personally think its because its a rule and not an actual formula like the ones you put in athe formula bar

[Doug Glancy]

In 2010, as I mentioned in my answer, the formula in the Rule column isn't going to change no matter which cell you click before opening the CF dialog, so what you describe isn't really the problem, it's the expected behavior. The best way to expand the range of the CF is by using the Applies to box, not by dragging. Is the range the formula is applied to showing correctly in the "Applies To" column? Make sure the "Show Formatting Rules for" box at the top says "This Worksheet" and that you haven't applied the same rule multiple times.