Conditional Formatting in 2007

[Bob Myers]

Need some help. Just switched to Excel 2007 and things seem to be
different.

Have a Range of cells of about 20 x 20. Two other cells, A1 & B1, have
numbers in them.

My conditional formatting is set up such that if any of the cells in the
Range = A1 it gets filled with one color, if it = B1 it gets filled with
another color, and if it <> OR(A1, B1) it gets no fill color.

Nothing happens when I change a value in either A1 or B1. But if I switch
to another Tab and then switch back, the conditional formatting is correct.
What am I missing?

Respectfully submitted,
Bob Myers

 

[~L]

If you paste your exact formulas, I can probably provide more spedific help.
From what you've written so far:

You don't need a rule for <>OR(A1,B1). I interpret your intention here to
be that if the cell value isn't equal to either A1 or B1 it doesn't get a
format.
If you don't set a rule, it simply won't apply formatting if the cells
aren't equal to A1 or B1 (the contents of your other two rules), which seems
to be what you want.
OR(A1,B1) will always return the logical value TRUE, which no number is
equal to.

If you want to apply a special format to cells that are not equal to A1 or
B1, if your range begins at A2, to apply this rule would look something like
(using the formula rule type):

=AND(A2<>$A$1,A2<>$B$1)

You must use absolute references on =A1 and =B1 or the value would update.
If your format range begins at A2, =A1 in a conditional format will check the
value of the row above each individual cell when applying the format and =B1
will check one row up and one column to the right.

 

[Bob Myers]

Let me simplify the problem. I'm working in Excel 2007.

I have a range C5:G30 on Sheet 1 with a bunch of numbers. The value in A1
is 2. I want a conditional format that will fill all the cells with 2s in
C5:G30 with Red. So I write in the Conditional Formatting Rules Manager the
following rule:

Cell value = $A$1 Format = Red Fill Applies to =
$C$5:$G$30 Stop If True = x (I've tried no x)

Now if I change the number in A1 to 4, nothing happens.

But if I switch to Sheet 2, and then switch back to Sheet 1, all the cells
in C5:G30 with 4s are Red Filled.

What am I missing? Why do I have to switch sheets to get conditional
formatting to work. It did not behave this way in Excel 2003. It's like an
option of some sort is set wrong. Calculation is in Automatic. Is there
something else?

Respectfully submitted,
Bob Myers

 

[Bob Myers]

The rest of the story (I've done a little more investigating).

The problem, as I have described it, only seems to occur on a worksheet
created in Excel 2003 running in Excel 2007. If I add a new worksheet next
to the one with the problem, then copy and paste the old worksheet with the
conditional formatting problem to the new worksheet, it works correctly
there.

It appears that all I have to do with the spreadsheet I started developing
in Excel 2003, is for every tab that has conditional formatting, is create a
new worksheet and copy the old worksheet onto it, delete the old one and
rename the new one. A pain in the backside, but it looks like it will solve
my problem.

Hope this helps somebody. I've wasted a lot of time on something that was
not backwards compatible -- and I'm running 2007 in Compatibility Mode.

Respectfully,
Bob Myers