conditional format confusion

[kualjo]

I'm not sure why this isn't working, but I'm sure someone here does. I have a cell in which a conditional format will switch between Number and Currency based on whether an adjoining cell reads Shares or Dollars. When I enter Dollars, the format will switch to Currency, but when I re-select Shares, it doesn't return to a Number format unless I double-click in the cell then click out of it. Why isn't this happening automatically?

For clarity, in the Conditional Formatting Rules Manager, I have the Dollar format above the Number, and neither of the Stop if True checkboxes is checked. I've tried various combinations of order and checks, but nothing seems to make it work.

BTW, this is Excel 2007.

 

[kualjo]

To further clarify: There are two rules, the first looking for Dollars and the second looking for Shares. If I switch the order, the problem simply shifts to the one that is second.

 

[kualjo]

nudge

 

[kualjo]

Not even one reply?! I'm really surprised; I thought this would be an easy one for this forum. Could it be that this is a bug of some kind in Excel? I could really use an answer for this. Anyone?

 

[AlisaA]

Ok, here is what I am seeing:

1) If you select the shares/dollars cell and type the name in, the formatted cell updates.
2) If you paste over the shares/dollars cell with the name, the formatted cell updates.
3) If you put in a Data Validation in the shares/dollars cell and select the new name, it works for the 1st change but not after that unless you either click in the shares/dollars cell or in the amount cell.
4) If you overwrite what is in the formula bar for the shares/dollars cell, it, again, works for the 1st change but not after that unless you either click in the shares/dollars cell or in the amount cell.

So, there is something about HOW the data gets into the shares/dollars cell that is causing the hangup. For the life of me, I can't understand what that is.

I know this doesn't help you solve your issue (sorry!!), but maybe this will jog someone's memory as to why the way the cell is updated has an impact on how the CF works...???

 

[chuckchuckit]

It sounds to me like a conditional formatting cross linking type of problem. Where perhaps you copied a conditional formatted row or a column to an other row or column. This may cross link them in ways you did not intend to to. If you do not want other rows or columns to affect each other, then you are safer to conditional format rows or columns (or ranges) one at a time. And "do not copy" them to save time because it may conditionally link them. I have had bugs like this before.

So I would try this: make a workbook copy of your original. In this copy I would delete both columns that have the associated problem. Then insert 2 new columns where they were. Then reformat those 2 new columns "one at a time" how you need them to be. Do not copy one to the other to save time doing the conditional formatting. Then see if the problem is gone.

If problem is gone, then something was linking those 2 cells and you may have to do the same to your original to get rid of the hidden problem.

Hope that helps.

 

[AlisaA]

It sounds to me like a conditional formatting cross linking type of problem. Where perhaps you copied a conditional formatted row or a column to an other row or column. This may cross link them in ways you did not intend to to. If you do not want other rows or columns to affect each other, then you are safer to conditional format rows or columns (or ranges) one at a time. And "do not copy" them to save time because it may conditionally link them. I have had bugs like this before.

So I would try this: make a workbook copy of your original. In this copy I would delete both columns that have the associated problem. Then insert 2 new columns where they were. Then reformat those 2 new columns "one at a time" how you need them to be. Do not copy one to the other to save time doing the conditional formatting. Then see if the problem is gone.

If problem is gone, then something was linking those 2 cells and you may have to do the same to your original to get rid of the hidden problem.

Hope that helps.

Unfortunately, I created a simple mockup of kualjo's issue in a brand-new, blank spreadsheet and had the same issues he was seeing. So, I do not think it is a linking issue...

 

[chuckchuckit]

Cells seem linked somehow. If there is no Worksheet Change Event involved, then I likely would keep deleting rows or columns one at a time (on a copy) to see when it is no longer linked. Or something along those lines.

Those bugs can be tough to find, but they are fixable if one keeps perhaps trying a different approach each time to solve it. Eventually through trial and error something will "unlink" them and that will be the clue to the problem perhaps.

 

[chuckchuckit]

Processing stock market data can end up being hundreds of rows or columns. That can take forever to format columns or rows one at a time. So I use macros to format columns or rows one at a time to avoid the potential cross link problems from conditional formatting or merged cells formatting etc. And also so that I do not accidentally format one of them incorrectly or forget a setting on one of them.

Lots of "Do While Loops" for me to build my spread sheets for eventual various stock data processing.

 

[kualjo]

Thanks for the responses folks!

Chuck, I was actually playing with a dummy scenario in a dummy workbook when I ran into this, so only the one cell had conditional formatting in it. No possibility of cross-formatting from anywhere else. There were only two possible entries (due to validation), so with the Stop if True check boxes unchecked and the first scenario not being true, the second one should kick in automatically. It doesn't. It makes no sense. And btw, there is no macro code behind it that would affect it in any way.

Hopefully, a solution is out there somewhere. Thanks again!