Conditional formatting behaving strangely

  • Thread starter Thread starter Danny J
  • Start date Start date
D

Danny J

Hi folks,

I set up a conditional format for a number of discontinous cells selecting
the cells using the CTRL key. the format worked fine....except that at
random intervals the cells are white rather than coloured (which is what I
set).

Any idea why?

Thanks,

Danny
 
Hi Danny,

I have never encountered a random response with CF - but I've led a
sheltered life!

It would be helpful if you reported the relevant CF formula.
 
Norman,

I've had some strange results too. I think in my case it might be a video
problem, because if I minimize the window, then bring it back, it looks as
it should. Or if I scroll the worksheet so the errant cells are off-screen,
then back, or switch worksheets, then back, it's OK then too. Try that when
yours fails. I'm using XL2002 with a Radeon 7500 dual-monitor video card.
Using XL2002.

Here's what fails:

Select column A
Format - Conditional formatting - Formula is =$C$1=1
Set it for Patterns - light yellow.

Now put 1 in C1, and the column turns yellow OK, except for A4:A16. Now
select column A, then deselect it. Now they're all yellow, as they should
be.

Not satisfied? Put 0 in C1. All the cells should turn white again. But
A4:A16 stay yellow. Again, select column A, then deselect it, and they're
all white, as they should be.
 
Hi Earl,

My first reaction on reading your post was to home in on the dual-monitor
video card. I was wrong!

I tried your tests and was (intermittently) able to reproduce your
observations, with a small difference: whereas your problem range comprised
cells A4:A16, in my case the corresponding range was A3:A16.

Extending your tests to a column range of A3:A65536, I observed analogous
results. I was also able to repeat the aberrant behaviour for
non-contiguous. multi-column ranges.

It may be that this behaviour is known, but it is news to me. However,
whilst I often use CF, I do not habitually use it over such extensive
ranges.
 
Norman,

I intended to respond to Danny, the OP. But while we're on this, does your
anomalous formatting straighten out if you minimize, then bring the window
back, and the other things I did?

I get a failure using Conditional formatting for only A1:A25. And even
smaller extents. And like your symptom, it's A3:A16 that fail to turn
yellow.
 
Hi Earl,
I intended to respond to Danny, the OP. But while we're on this, does
your anomalous formatting straighten out if you minimize, then bring the
window back, and the other things I did?
Click to expand...

Yes - at least for the extended ranges previously discussed.
I get a failure using Conditional formatting for only A1:A25. And even
smaller extents. And like your symptom, it's A3:A16 that fail to turn
yellow.
Click to expand...

I too could reproduce this.

In case there was some residual memory effect at play, I opened a new
workbook. I applied the same CF condition (=$C$1=1) to cells A1:A5. I
entered a value of 1 in cell C1. I then changed the value of C1 to zero. Lo
and behold, cells A1:A2 lost the CF shading, but cells A3:A10 retained it.
Now, highlighting cells A5: A7 and then clicking a cell in column B, cells
A5:A7 lose the shading. Cells A3:A4 and A8:A10 remain aberrantly shaded.
Restoring the value of C1 to 1, all the cells in the range A1:A10 are shaded
EXCEPT for the cells previously highlighted, namel A5:A7!

Tell me that I am hallucinating!
 
Hi Earl,

Still suspecting some residual memory/video effect, I closed and re-opened
Excel. In a virgin workbook I then set up CF, as before, for the range
A1:A10 with immediately evident problems.

I closed Excel, rebooted the machine, opened a new workbook and set CF for
A1:A10. On entering 1 in C1, Cells A1:A2 are shaded, A3:A10 are not.
Double clicking these latter cells sequentially results in the CF shading
with, however, gridlines grinning through; cells A1:A2 remain shaded
'normally'.

I was able to reproduce these latter results, in a new worksheet, on columns
other than column A and with trigger cells other than C1.

Finally, it appears that copying the CF range, either to itself or
elsewhere, eradicates all of the observed problems
 
Hi Danny,

As a *possible* workaround, try selecting a range large enough to encompass
the discontinuous CF range and copy it to itself.
 
What was the formula?
say the selected cells were A1:a10, a12:a21, a23:a32 and the formula If the
value is between 0% and 50% then colour the cell blue. Condition 2. If the
cell value is between 51% and 100% colour the cell red.

Thanks :-)

Danny
 
Hi Danny,

Did you try the copy / paste workaround suggested in my seond response to
your post?
 
Back
Top