Conditional Formatting - Formula gives #REF!

**brijazz** · 03-21-2009, 11:13 AM

I've written a conditional formatting formula as follows:

=(AND(B4="",A4<>""))

I copy and paste the formula into adjacent columns, and, as I always want the formula to check if the contents of column A are blank, I manually change the formula to reference A4. So, I end up with formulas as follow:

=(AND(B4="",A4<>""))
=(AND(C4="",A4<>""))
=(AND(D4="",A4<>""))
etc...

Everything works the way I want, and I save my work. However, when I re-open the document later on, all of my A4 values have changed to #REF!, so that my formulas now as follow:

=(AND(B4="",#REF!<>""))
=(AND(C4="",#REF!<>""))
=(AND(D4="",#REF!<>""))

Why is Excel taking it upon itself to change some of my values into #REF!? How can I fix this? It's driving me crazy...

**daddylonglegs** · 03-21-2009, 12:01 PM

You shouldn't need to adjust the formula for different columns, try selecting the whole range first and then applying the formula that applies to the top left cell, with a $ in front of A4 to stop it changing from column to column, e.g.

=AND(B4="",$A4<>"")

Now you've finished, nothing to copy or adjust......