I have a workbook in which I have a number of cells which have 2 conditional format conditions each.

if C6 < B6 then backcolor = blue and if C6 > B6 then backcolor = yellow.  The cell values are calculated averages against of range of cells on another sheet within the same workbook.

Here's the problem.  The formulas keep changing.  I'll open the sheet and the formatting is correct.  Then refresh the data and suddenly some of the formatted cells (not all) change such that the first condition above now creates a backcolor of yellow, while the second condition now formats the cell as blue.  In other words, the < or > argument reverses.  I've fixed these conditional formats several times, but the switch still occurs.

At one point during the creation of this sheet, I used copy & paste to apply the formatting to these cells; but when I review the individual formulas the referenced cells are correct.  I've also confirmed each time that I corrected the conditional formulas that I was only changing the "selected cell" format.

This is really frustrating since I have a dozen or more of these on each of my dashboard workbooks and have to update a dozen workbooks every week.

And before you ask, while I refuse to call myself a guru, I'm am an expert with Excel and I do know what the H*&$ I'm doing.

Has anyone else seen anything like

Could you please post that formula to check? Also check the below detailed article for conditional formatting in excel:

https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/#conditional-formatting-not-worki

Thanks, Fouad.

The formula is as simple as I mentioned above.  I did try making the cell references absolute, but that didn't seem to make a difference.

I read through the link you sent, thanks.  The only tidbit I found in it that might apply (since I've verified all the others) is "4. Check the rule you created. Double-check the rule in the Conditional Formatting Rules Manager. Sometimes, for no reason at all, Microsoft Excel distorts the rule you have just created".  Indeed, Excel does appear to be 'distorting' my rule.

I'll keep looking. . . .

I wanted the formula syntax as it, or if you want upload a sample file to onedrive or other, because like this it will be a guess from our side, unless other messengers have faced something similar.

Hi Fouad,

Thanks for posting in MSDN froum.

This forum is for developers discusing developing issues involve Excel application, since the issue is more relative to end-user, I would like to move it to Technet forums - Excel IT Pro Discussions - Microsoft forum.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thanks for your understanding.

Regards & Fei