Conditional Formatting & Dynamic Range

[james]

Hello everyone:

I am learning to highlight duplicates in dynamic range. The problem I have
is that the highlighting is not been applied consistently to the extended
cell values.

In the Name-Define-Refers-to box I entered =OFFSET($A$1,0,0,COUNTA($A:$A),1)
for the dynamic range (Range1). I put duplicates in A1 to A5.

I highlighted A1 to A5 and put =IF(COUNTIF(Range1,A1)>1,TRUE,FALSE) in the
Formula Is box with format blue. It worked. The duplicates are highlighted
blue.

But as I extended the range with entries, intentionally separating the
duplicates, some turned blue and some not. I used the "goto" to check the new
range. The New Range (Range1) covered the new entries but the new duplicates
did not change to blue.

Data: A1 to A5, I entered 1, 2, 2, 3, 4. The 2s went blue. Then: from A6 to
A10, I entered say 545, 2, 855, 2, 2. The first (A7) 2 went blue but not the
latter 2s.

Any help will be appreciated. Thank you in advance.

james

 

[Biff]

Hi!

This works just fine for me. I suspect the problem may be the the way you
"extended" the conditional formatting.

=IF(COUNTIF(Range1,A1)>1,TRUE,FALSE)

You can shorten that to:

=COUNTIF(Range1,A1)>1

How did you extend the conditional formatting?

Biff

 

[james]

Thanks. It worked with "numbers" now once I shortened the formula as you
suggested.

However, if I were to mix the column with text and numbers, the duplicated
text still not higlighted. But it is good enough for me for the current
purpose.

I suppose I extended the conditional formatting to the added cells by the
use and referrence of dynamic range name.

Best regards.

james