Struggling with Conditional Formatting

[Timoo]

Hi Guys,

A collegue has got a weird problem with Conditional Formatting.
While color coding rows row by row, every time he saves the sheet, the color coding gets mixed up.
Where he fills in row per row, Excel automagically transforms it to what Excel thinks will be "similar".
Or so it seems...

I would llike to attach 2 images, but do not how to?
(currently no ftp-server to my availability to upload these pics)

With kind regards,
Timo


</SPAN>

 

[Worf]

Hello and welcome to the Board

1) What Excel version are you using?

2) You can upload your files to any online hosting service such as DropBox, MediaFire, SkyDrive and others. Then you paste the link by clicking the Link button on the Reply toolbar:


https://www.dropbox.com/sh/uuj8geuogk2vygs/75HVQEf13D

There is also an Insert Image button that accepts an URL address.

 

[Timoo]

Hello and welcome to the Board

1) What Excel version are you using?

2) You can upload your files to any online hosting service such as DropBox, MediaFire, SkyDrive and others. Then you paste the link by clicking the Link button on the Reply toolbar:


https://www.dropbox.com/sh/uuj8geuogk2vygs/75HVQEf13D

There is also an Insert Image button that accepts an URL address.

Sorry, I indeed forgot to mention.
Excel 2010, Windows 7 Enterprise environment.
And the fact I did not have any FTP ready at hand, got me fired up again, so: pics

What it should be:

What happens after saving & reopening:

 

[Worf]

I don’t have a copy of Excel 2010, but one thing that is puzzling me is that it appears to be a mix of relative and absolute references in your formulas.
Contrary to Excel 2007, I see that your version doesn´t display the actual formulas on the Rules Manager dialog, but anyway:

- C7 is a relative reference and will be adjusted (changed)
- $Z$2 is an absolute reference, therefore fixed.

So I would expect the first rule to be Cell Value <= $C$7

Could you verify this?

 

[Timoo]

Hi,

Thank you for willing to help me out in this!

Cell C7 is relative to the line conditionally formatted.
C7 is compared to C5, D7 to D5, E7 to E5, and so on.
Z2 is fixed value; percentage.

If C5 <= C7, green
If C5 > C7*% (Z2), red
If C5 > C7, yellow

Image below shows it.
Although it is linked not to Z2, but to XX on tab 'ALL..blabla'.
We are currently checking if that is the problem, but it seems not to be the case.

with kind regards,
Timo

 

[Worf]

Hi Timo

According to your explanation cell C7 is indeed a relative reference. I just wanted to be sure the logic was right.

• Do you have access to any machine with Excel 2007 installed? You could test the workbook using another version to check if the problem is specific to Excel 2010.
• Can you share this workbook with me or it contains sensitive data? It will be difficult for me to reproduce the error.