Conditional formatting behaving strangely

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
 
N

Norman Jones

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.
 
E

Earl Kiosterud

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.
 
N

Norman Jones

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.
 
E

Earl Kiosterud

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.
 
N

Norman Jones

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!
 
N

Norman Jones

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
 
N

Norman Jones

Hi Danny,

As a *possible* workaround, try selecting a range large enough to encompass
the discontinuous CF range and copy it to itself.
 
D

Danny J

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
 
N

Norman Jones

Hi Danny,

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