Conditional Formatting & Dynamic Range

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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


"Biff" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>