In List View, conditional formatting based on Get (Found Count) is not applied correctly when the addition or deletion of records changes the condition. For example: This may be the symptom of a redraw issue.Deleting and adding records does not require the layout to redraw, and therefore does will not reevaluate conditional formatting on these unaffected fields.Earlier this year, I posted a tip for separating dates in a list, by using a red border at the start of a date change.It’s a technique that I use in one of my own workbooks, which I update every morning.But this week I ran into a conditional formatting crisis, and had to start from scratch.Fortunately, the fix didn’t take too long, but with complex formatting, things could have been much worse.And you can’t specify row fields in this dialog, so you’ll have define the formats again for those areas.And if you alter the formats you’ll have to do it all again.
In this example, cells with employee ID numbers who have certification dates due to expire within 60 days are formatted in yellow, and ID numbers of employees with an expired certification are formatted in red. The first rule (which, if True, sets cell background color to red) tests a date value in column B against the current date (obtained by using the TODAY function in a formula).
For these reasons I’d rather just apply the conditional formatting to the row headings and the values area in one fell swoop.
But I don’t want to visit the condtional formatting dialog to re-expand the range each time a pivot table is refreshed.
When you create more than one conditional formatting rule for a range of cells, it helps to understand in what order these rules are evaluated, what happens when two or more rules conflict, how copying and pasting can affect rule evaluation, how to change the order in which rules are evaluated, and when to stop rule evaluation.
You create, edit, delete, and view all conditional formatting rules in the workbook by using the Conditional Formatting Rules Manager dialog box.