Highlight duplicate cells in a named range with conditional formatting

[jsgeare]

Excel 2018 conditional formatting for duplicates is limited to the number of rows at the time the conditional formatting is applied, whether by a formula (such as COUNTIF) or by selection of the highlight duplicates rule. Using a named range in a formula simply reverts to the actual cells within the range such as $B$2:$B$435.

The problem, of course, is that as the named range grows by insertion of more rows, the conditional formatting formula (or rule) does not grow to reflect the additional rows.

Is there a non-VBA work around, for this, such that formatting is based on the current number of rows, as opposed to the number of them when the rule or formula was applied?

Thanks in advance for any help.

 

[Joe4]

Re: Highlight duplicate cells in a named range with conditional formatting - how?

The problem, of course, is that as the named range grows by insertion of more rows, the conditional formatting formula (or rule) does not grow to reflect the additional rows.

In my experience, it does if the new rows and inserted within the table. The issue usually happens when the new rows are added after the end of the data (hence, they are not being inserted into the existing range).
A few things you may want to try to avoid this:
- Insert the new rows BEFORE the last row of current data
- Have your named range (or Conditional Formatting range) extend beyond the end of your data, so that new rows being added would be included
- Use VBA to re-define your range every time you add a new row