Finding duplicates in a column

I wrote a small bit of code to compare the active cell to the cell below it,
and if it matches make the column red. The problem is that it doesn't work,
it highlights cells that don't match any around them and doesn't pick up ones
that do.
Does anyone have some better code?

Set RnG1 = ActiveCell
Set RnG2 = RnG1.Offset(1, 0)

If RnG1.Value = RnG2.Value Then
RnG2.Interior.ColorIndex = 3
Else
'Not make it red
End If

Chris wrote:

> I wrote a small bit of code to compare the active cell to the cell below it,
> and if it matches make the column red. The problem is that it doesn't work,
> it highlights cells that don't match any around them and doesn't pick up ones
> that do.
> Does anyone have some better code?

obviously if you are eventing this on worksheet_change then you need to
take account that it will not fire until enter or tab away from cell,
and you would need to skip the active cell back or up depending on
enter or tab.

If you know what column it is always in then set the With to the column
change and then select two cases one for enter and one for tab, (on
change to target, if is column 2 then go back, if is column 1 then go
up)

Does this make sense? I am sure you have already provided for this,
unless of course it is eventing from a button and in that case the cell
would not move.

Hope this helps

Duncan


Duncan wrote:

> Set RnG1 = ActiveCell
> Set RnG2 = RnG1.Offset(1, 0)
>
> If RnG1.Value = RnG2.Value Then
> RnG2.Interior.ColorIndex = 3
> Else
> 'Not make it red
> End If
>
> Chris wrote:
>
> > I wrote a small bit of code to compare the active cell to the cell below it,
> > and if it matches make the column red. The problem is that it doesn't work,
> > it highlights cells that don't match any around them and doesn't pick up ones
> > that do.
> > Does anyone have some better code?

I just noticed you said make the column red, that would be small change
to:

RnG2.EntireColumn.Interior.ColorIndex = 3

Post back let us know how you get on and if this solves or not

Duncan

Duncan wrote:

> obviously if you are eventing this on worksheet_change then you need to
> take account that it will not fire until enter or tab away from cell,
> and you would need to skip the active cell back or up depending on
> enter or tab.
>
> If you know what column it is always in then set the With to the column
> change and then select two cases one for enter and one for tab, (on
> change to target, if is column 2 then go back, if is column 1 then go
> up)
>
> Does this make sense? I am sure you have already provided for this,
> unless of course it is eventing from a button and in that case the cell
> would not move.
>
> Hope this helps
>
> Duncan
>
>
> Duncan wrote:
>
> > Set RnG1 = ActiveCell
> > Set RnG2 = RnG1.Offset(1, 0)
> >
> > If RnG1.Value = RnG2.Value Then
> > RnG2.Interior.ColorIndex = 3
> > Else
> > 'Not make it red
> > End If
> >
> > Chris wrote:
> >
> > > I wrote a small bit of code to compare the active cell to the cell below it,
> > > and if it matches make the column red. The problem is that it doesn't work,
> > > it highlights cells that don't match any around them and doesn't pick up ones
> > > that do.
> > > Does anyone have some better code?