conditional format confusion

kualjo

Board Regular
Joined
Aug 15, 2006
Messages
106
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

kualjo

Board Regular
Joined
Aug 15, 2006
Messages
106
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

Board Regular
Joined
Aug 15, 2006
Messages
106
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

Board Regular
Joined
Mar 17, 2010
Messages
193

ADVERTISEMENT

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

Well-known Member
Joined
Sep 18, 2010
Messages
541
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

Board Regular
Joined
Mar 17, 2010
Messages
193

ADVERTISEMENT

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

Well-known Member
Joined
Sep 18, 2010
Messages
541
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

Well-known Member
Joined
Sep 18, 2010
Messages
541
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

Board Regular
Joined
Aug 15, 2006
Messages
106
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!
 

Forum statistics

Threads
1,141,849
Messages
5,708,973
Members
421,602
Latest member
jkpce1880

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top