Conditional Formatting Rules Simplified

re: Conditional Formatting Rules Simplified

ShelLuser — Tue, 28 Feb 2012 17:08:48 GMT

Your formula seems to contain an error. "m5$A$1" points to no where. The OR formule needs a logical operand, but simply putting 2 cells in there isn't counted as such. Same applies to IF, it needs a condition yet you didn't specify it (note how "=1" sits outside the IF formule.

re: Conditional Formatting Rules Simplified

ShelLuser — Tue, 28 Feb 2012 17:01:11 GMT

It sounds to me as if you want "partial relative formatting". So; you want a row of numbers (I to M) to be checked with the main number residing in column G, right ? And this can apply to several rows. (2 to 10 for exampe).

In that case the same example as above would apply, but you'd want to fix the column to check ('G') while the row (2 to 10) should remain variable (so that the same rule can be applied onto the whole range ($i$2:$m$10 for example)).

So you'd use cell value $G2 which is then applied to your range of $I$2:$M$10.

re: Conditional Formatting Rules Simplified

Rachael_24 — Fri, 04 Feb 2011 14:49:45 GMT

Can anyone help?

I want to format a table in a particular way. The font in the table is set as webdings so that if someone puts an "a" in a cell, it "ticks" the cell. What I want to do is format the table so that when I "tick" a cell, the whole row is highlighted.

re: Conditional Formatting Rules Simplified

Andy Viohl — Sun, 16 May 2010 12:49:25 GMT

Just found that after the Excel file is saved and reopened, the Rules Manager will consolidate accordingly the rules and so Excel 2007 will show the rules as I would expect it to, so there is no inappropriate behavior of Excel 2007 for the second method that I show in my demo file. Still, for the first method used (the same that I use in Excel 2003 and the method that I would say is the more intuitive to use), there is the bug: a cell that has the conditional formatting applied to it when it should have not had it. Maybe there is some setting in Excel 2007 telling it to extend formatting to neighboring cells? That would explain the "bug".

re: Conditional Formatting Rules Simplified

Andy Viohl — Sun, 16 May 2010 12:27:35 GMT

Hi,

I don't know about Office 2010, I don't have it yet. But I have a comment about Office 2007 and since I assume that it has been passed on to 2010, I will post it anyway. Despite being simple to show, it's difficult to put the problem into words so I created an Excel demo book to show it. Please, grab it at www.viol.com/.../bugexcel.xls. Basically, the problem is that Excel 2007 shows a bug when copying a range of cells with conditional formatting; it applies the formatting to a cell that shouldn't; there is a workaround but even so Excel 2007 behavior, regarding copying the conditionally formatted cells, seems not appropriate. Excel 2003, on the other hand, does it all right. I wonder if Excel 2010 shows the same behavior as Excel 2007.

re: Conditional Formatting Rules Simplified

ded — Tue, 04 May 2010 07:35:18 GMT

Nice...can't wait to use this on MY sales data! :)

re: Conditional Formatting Rules Simplified

Amit Velingkar [MSFT] — Fri, 30 Apr 2010 04:43:25 GMT

@Jan: Thanks for the comment. Currently, CF is designed to work as a group and any chnages apply to the entire group. I will add your request for consideration for the next version of Excel.

@Buck: Thanks for giving us an insight into your user scenario.

re: Conditional Formatting Rules Simplified

Buck Dossey — Wed, 28 Apr 2010 20:30:09 GMT

Nice article. This type of conditional formatting is relevant for any expression that evaluates to true. I've found much value in using dates for status sheets to automatically change the color of a row if a status item has not been completed by the due date.

re: Conditional Formatting Rules Simplified

Jan Karel Pieterse — Wed, 28 Apr 2010 07:54:05 GMT

Amit: Nice article.

One thing that I'd like to see explained: How do I edit a conditional formatting rule of ONE cell, even when the same rule has also been applied to other cells.

In the CF manager, the "Applies to" box shows reference to all cells that have that rule. If I change that to the cell I want to edit, the CF is removed from the other cells.

re: Conditional Formatting Rules Simplified

Amit Velingkar [MSFT] — Tue, 27 Apr 2010 19:00:32 GMT

@Ken: The ModifyAppliesToRange method on the FormatConditions object should let you modify the AppliedTo range. Also, we have made changes in Excel 2010 that merges duplicate rule on copy and paste. This should help mitigate the stacking effect and the resulting performance degradation. Also, there are performance improvements in Excel 2010 for CF evaluation.

@Jacob: Thanks Jacob. You are absolutely right. Thanks for the comment. Although, on re-entry in the CF rules manager dialog, we always display the formula relative to the top-left cell.