I'm trying to make a script that will run when I edit a cell. Once I've
edited the cell I want the script to go to the top of the column and then
check to see if that value already exists anywhere in the target column. If
it does exist already then i want it to highlight it in red as well as the
cell that I just edited.

It's going to be similar to the find duplicates in selected range
conditional formatting rule. But the selected range needs to be the target
column. If the same value exists in a different column then it shouldn't
even be concerned with it.

Here's what I have so far. I'm trying to use some sort of loop to cycle
through each cell in the target column and compare it to the value of the
cell that I just changed.


----------------------------------------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

tRow = Target.Row
Row = 1
tColumn = Target.Column


Cell1 = Sh.Cells(tRow, tColumn).Value

For Each cell In Columns(tColumn).Select

Cell2 = Sh.Cells(Row, tColumn).Value

If Cell1.Value = Cell2.Value Then

'.PatternColorIndex = x1Automatic
Sh.Cells(Cell2).Interior.ColorIndex = 4

End If

Row = Row + 1

Next

End Sub
--------------------------------------------------------------------------------------------

Can anyone help me out?

I didn't look at al the code but you can't pass the sht into the worksheet
change function. This function is defined by excel. the worksheet change
function must be place in the VBA sheet that you are going to use it on. If
it is used on more than one sheet then you need to put it on each sheet.
Because you kn ow which sheet it is being used on you don't have to reference
the sheet or the workbook.

"Jonathan Brown" wrote:

> I'm trying to make a script that will run when I edit a cell. Once I've
> edited the cell I want the script to go to the top of the column and then
> check to see if that value already exists anywhere in the target column. If
> it does exist already then i want it to highlight it in red as well as the
> cell that I just edited.
>
> It's going to be similar to the find duplicates in selected range
> conditional formatting rule. But the selected range needs to be the target
> column. If the same value exists in a different column then it shouldn't
> even be concerned with it.
>
> Here's what I have so far. I'm trying to use some sort of loop to cycle
> through each cell in the target column and compare it to the value of the
> cell that I just changed.
>
>
> ----------------------------------------------------------------------------------------------
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>
> tRow = Target.Row
> Row = 1
> tColumn = Target.Column
>
>
> Cell1 = Sh.Cells(tRow, tColumn).Value
>
> For Each cell In Columns(tColumn).Select
>
> Cell2 = Sh.Cells(Row, tColumn).Value
>
> If Cell1.Value = Cell2.Value Then
>
> '.PatternColorIndex = x1Automatic
> Sh.Cells(Cell2).Interior.ColorIndex = 4
>
> End If
>
> Row = Row + 1
>
> Next
>
> End Sub
> --------------------------------------------------------------------------------------------
>
> Can anyone help me out?

I've got that part working fine. The code runs whenever a cell is modified,
or whenever the sheet is changed. The part where the script is currently
choking is on the If sh.cells(cell1).value = sh.cells(cell2).value statement.
I'm getting an error saying "Run-time error '1004': application-defined or
object-defined error".

I've made some changes and it's working better but it's still giving me the
above error message. The code is now looking like below:

------------------------------------------------------------------------------------------------
tRow = Target.Row
Row = 1
tColumn = Target.Column

Cell1 = Sh.Cells(tRow, tColumn)

For Each cell In Sh.Cells(Row, tColumn)

Cell2 = Sh.Cells(Row, tColumn)

If Sh.Cells(Cell1) = Sh.Cells(Cell2) Then

Sh.Cells(Cell2).Interior.ColorIndex = 4
Sh.Cells(Cell1).Interior.ColorIndex = 4

End If

Row = Row + 1

Next

-----------------------------------------------------------------------------------------------



"Joel" wrote:

> I didn't look at al the code but you can't pass the sht into the worksheet
> change function. This function is defined by excel. the worksheet change
> function must be place in the VBA sheet that you are going to use it on. If
> it is used on more than one sheet then you need to put it on each sheet.
> Because you kn ow which sheet it is being used on you don't have to reference
> the sheet or the workbook.
>
> "Jonathan Brown" wrote:
>
> > I'm trying to make a script that will run when I edit a cell. Once I've
> > edited the cell I want the script to go to the top of the column and then
> > check to see if that value already exists anywhere in the target column. If
> > it does exist already then i want it to highlight it in red as well as the
> > cell that I just edited.
> >
> > It's going to be similar to the find duplicates in selected range
> > conditional formatting rule. But the selected range needs to be the target
> > column. If the same value exists in a different column then it shouldn't
> > even be concerned with it.
> >
> > Here's what I have so far. I'm trying to use some sort of loop to cycle
> > through each cell in the target column and compare it to the value of the
> > cell that I just changed.
> >
> >
> > ----------------------------------------------------------------------------------------------
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >
> > tRow = Target.Row
> > Row = 1
> > tColumn = Target.Column
> >
> >
> > Cell1 = Sh.Cells(tRow, tColumn).Value
> >
> > For Each cell In Columns(tColumn).Select
> >
> > Cell2 = Sh.Cells(Row, tColumn).Value
> >
> > If Cell1.Value = Cell2.Value Then
> >
> > '.PatternColorIndex = x1Automatic
> > Sh.Cells(Cell2).Interior.ColorIndex = 4
> >
> > End If
> >
> > Row = Row + 1
> >
> > Next
> >
> > End Sub
> > --------------------------------------------------------------------------------------------
> >
> > Can anyone help me out?

"Joel" wrote:

> I didn't look at al the code but you can't pass the sht into the worksheet
> change function. This function is defined by excel. the worksheet change
> function must be place in the VBA sheet that you are going to use it on. If
> it is used on more than one sheet then you need to put it on each sheet.
> Because you kn ow which sheet it is being used on you don't have to reference
> the sheet or the workbook.
>
> "Jonathan Brown" wrote:
>
> > I'm trying to make a script that will run when I edit a cell. Once I've
> > edited the cell I want the script to go to the top of the column and then
> > check to see if that value already exists anywhere in the target column. If
> > it does exist already then i want it to highlight it in red as well as the
> > cell that I just edited.
> >
> > It's going to be similar to the find duplicates in selected range
> > conditional formatting rule. But the selected range needs to be the target
> > column. If the same value exists in a different column then it shouldn't
> > even be concerned with it.
> >
> > Here's what I have so far. I'm trying to use some sort of loop to cycle
> > through each cell in the target column and compare it to the value of the
> > cell that I just changed.
> >
> >
> > ----------------------------------------------------------------------------------------------
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >
> > tRow = Target.Row
> > Row = 1
> > tColumn = Target.Column
> >
> >
> > Cell1 = Sh.Cells(tRow, tColumn).Value
> >
> > For Each cell In Columns(tColumn).Select
> >
> > Cell2 = Sh.Cells(Row, tColumn).Value
> >
> > If Cell1.Value = Cell2.Value Then
> >
> > '.PatternColorIndex = x1Automatic
> > Sh.Cells(Cell2).Interior.ColorIndex = 4
> >
> > End If
> >
> > Row = Row + 1
> >
> > Next
> >
> > End Sub
> > --------------------------------------------------------------------------------------------
> >
> > Can anyone help me out?

I've got that part working fine. The code runs whenever a cell is modified,
or whenever the sheet is changed. The part where the script is currently
choking is on the If sh.cells(cell1).value = sh.cells(cell2).value statement.
I'm getting an error saying "Run-time error '1004': application-defined or
object-defined error".

I've made some changes and it's working better but it's still giving me the
above error message. The code is now looking like below:

------------------------------------------------------------------------------------------------
tRow = Target.Row
Row = 1
tColumn = Target.Column

Cell1 = Sh.Cells(tRow, tColumn)

For Each cell In Sh.Cells(Row, tColumn)

Cell2 = Sh.Cells(Row, tColumn)

If Sh.Cells(Cell1) = Sh.Cells(Cell2) Then

Sh.Cells(Cell2).Interior.ColorIndex = 4
Sh.Cells(Cell1).Interior.ColorIndex = 4

End If

Row = Row + 1

Next

-----------------------------------------------------------------------------------------------

Simple:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long

For x = 1 To Target.Row - 1

If Target.Value = Range(Cells(x, Target.Column).Address).Value Then
Cells(x, Target.Column).Interior.ColorIndex = 4
Target.Interior.ColorIndex = 4
End If

Next x

End Sub


"Jonathan Brown" wrote:

> I'm trying to make a script that will run when I edit a cell. Once I've
> edited the cell I want the script to go to the top of the column and then
> check to see if that value already exists anywhere in the target column. If
> it does exist already then i want it to highlight it in red as well as the
> cell that I just edited.
>
> It's going to be similar to the find duplicates in selected range
> conditional formatting rule. But the selected range needs to be the target
> column. If the same value exists in a different column then it shouldn't
> even be concerned with it.
>
> Here's what I have so far. I'm trying to use some sort of loop to cycle
> through each cell in the target column and compare it to the value of the
> cell that I just changed.
>
>
> ----------------------------------------------------------------------------------------------
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>
> tRow = Target.Row
> Row = 1
> tColumn = Target.Column
>
>
> Cell1 = Sh.Cells(tRow, tColumn).Value
>
> For Each cell In Columns(tColumn).Select
>
> Cell2 = Sh.Cells(Row, tColumn).Value
>
> If Cell1.Value = Cell2.Value Then
>
> '.PatternColorIndex = x1Automatic
> Sh.Cells(Cell2).Interior.ColorIndex = 4
>
> End If
>
> Row = Row + 1
>
> Next
>
> End Sub
> --------------------------------------------------------------------------------------------
>
> Can anyone help me out?

This is the workbook_sheetchange event that goes under the ThisWorkbook module.

It's not the worksheet_change event which could be in any/all worksheets.

Joel wrote:
>
> I didn't look at al the code but you can't pass the sht into the worksheet
> change function. This function is defined by excel. the worksheet change
> function must be place in the VBA sheet that you are going to use it on. If
> it is used on more than one sheet then you need to put it on each sheet.
> Because you kn ow which sheet it is being used on you don't have to reference
> the sheet or the workbook.
>
> "Jonathan Brown" wrote:
>
> > I'm trying to make a script that will run when I edit a cell. Once I've
> > edited the cell I want the script to go to the top of the column and then
> > check to see if that value already exists anywhere in the target column. If
> > it does exist already then i want it to highlight it in red as well as the
> > cell that I just edited.
> >
> > It's going to be similar to the find duplicates in selected range
> > conditional formatting rule. But the selected range needs to be the target
> > column. If the same value exists in a different column then it shouldn't
> > even be concerned with it.
> >
> > Here's what I have so far. I'm trying to use some sort of loop to cycle
> > through each cell in the target column and compare it to the value of the
> > cell that I just changed.
> >
> >
> > ----------------------------------------------------------------------------------------------
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >
> > tRow = Target.Row
> > Row = 1
> > tColumn = Target.Column
> >
> >
> > Cell1 = Sh.Cells(tRow, tColumn).Value
> >
> > For Each cell In Columns(tColumn).Select
> >
> > Cell2 = Sh.Cells(Row, tColumn).Value
> >
> > If Cell1.Value = Cell2.Value Then
> >
> > '.PatternColorIndex = x1Automatic
> > Sh.Cells(Cell2).Interior.ColorIndex = 4
> >
> > End If
> >
> > Row = Row + 1
> >
> > Next
> >
> > End Sub
> > --------------------------------------------------------------------------------------------
> >
> > Can anyone help me out?

--

Dave Peterson

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim HowMany As Long
Dim myCell As Range

'clear existing colors???
Target.EntireColumn.Interior.ColorIndex = xlNone

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Target.Value = "" Then
Exit Sub
End If

HowMany = Application.CountIf(Target.EntireColumn, Target.Value)

If HowMany = 1 Then
'ok, just the new value
Exit Sub
End If

For Each myCell In Intersect(Sh.UsedRange, Target.EntireColumn)
If myCell.Value = Target.Value Then
myCell.Interior.ColorIndex = 4
End If
Next myCell

End Sub


Jonathan Brown wrote:
>
> I'm trying to make a script that will run when I edit a cell. Once I've
> edited the cell I want the script to go to the top of the column and then
> check to see if that value already exists anywhere in the target column. If
> it does exist already then i want it to highlight it in red as well as the
> cell that I just edited.
>
> It's going to be similar to the find duplicates in selected range
> conditional formatting rule. But the selected range needs to be the target
> column. If the same value exists in a different column then it shouldn't
> even be concerned with it.
>
> Here's what I have so far. I'm trying to use some sort of loop to cycle
> through each cell in the target column and compare it to the value of the
> cell that I just changed.
>
> ----------------------------------------------------------------------------------------------
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>
> tRow = Target.Row
> Row = 1
> tColumn = Target.Column
>
> Cell1 = Sh.Cells(tRow, tColumn).Value
>
> For Each cell In Columns(tColumn).Select
>
> Cell2 = Sh.Cells(Row, tColumn).Value
>
> If Cell1.Value = Cell2.Value Then
>
> '.PatternColorIndex = x1Automatic
> Sh.Cells(Cell2).Interior.ColorIndex = 4
>
> End If
>
> Row = Row + 1
>
> Next
>
> End Sub
> --------------------------------------------------------------------------------------------
>
> Can anyone help me out?

--

Dave Peterson

This works too:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim x As Long

For x = 1 To Target.Row - 1

If Target.Value = Range(Cells(x, Target.Column).Address).Value Then
Cells(x, Target.Column).Interior.ColorIndex = 4
Target.Interior.ColorIndex = 4
End If

Next x

End Sub


"Kent Prokopy" wrote:

> Simple:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim x As Long
>
> For x = 1 To Target.Row - 1
>
> If Target.Value = Range(Cells(x, Target.Column).Address).Value Then
> Cells(x, Target.Column).Interior.ColorIndex = 4
> Target.Interior.ColorIndex = 4
> End If
>
> Next x
>
> End Sub
>
>
> "Jonathan Brown" wrote:
>
> > I'm trying to make a script that will run when I edit a cell. Once I've
> > edited the cell I want the script to go to the top of the column and then
> > check to see if that value already exists anywhere in the target column. If
> > it does exist already then i want it to highlight it in red as well as the
> > cell that I just edited.
> >
> > It's going to be similar to the find duplicates in selected range
> > conditional formatting rule. But the selected range needs to be the target
> > column. If the same value exists in a different column then it shouldn't
> > even be concerned with it.
> >
> > Here's what I have so far. I'm trying to use some sort of loop to cycle
> > through each cell in the target column and compare it to the value of the
> > cell that I just changed.
> >
> >
> > ----------------------------------------------------------------------------------------------
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >
> > tRow = Target.Row
> > Row = 1
> > tColumn = Target.Column
> >
> >
> > Cell1 = Sh.Cells(tRow, tColumn).Value
> >
> > For Each cell In Columns(tColumn).Select
> >
> > Cell2 = Sh.Cells(Row, tColumn).Value
> >
> > If Cell1.Value = Cell2.Value Then
> >
> > '.PatternColorIndex = x1Automatic
> > Sh.Cells(Cell2).Interior.ColorIndex = 4
> >
> > End If
> >
> > Row = Row + 1
> >
> > Next
> >
> > End Sub
> > --------------------------------------------------------------------------------------------
> >
> > Can anyone help me out?

Dave,

The procedure you provided is working for finding duplicates and changing
the highlight color, but it'll still highlight if there's a duplicate on the
row. All I want is for it to find a duplicate in the column. If there's two
cells side by side with the same value then I don't want it to change the
color. but if I have two cells above and below each other then I do want it
to change it's color.

could you help me with that?


"Dave Peterson" wrote:

> Option Explicit
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>
> Dim HowMany As Long
> Dim myCell As Range
>
> 'clear existing colors???
> Target.EntireColumn.Interior.ColorIndex = xlNone
>
> If Target.Cells.Count > 1 Then
> Exit Sub
> End If
>
> If Target.Value = "" Then
> Exit Sub
> End If
>
> HowMany = Application.CountIf(Target.EntireColumn, Target.Value)
>
> If HowMany = 1 Then
> 'ok, just the new value
> Exit Sub
> End If
>
> For Each myCell In Intersect(Sh.UsedRange, Target.EntireColumn)
> If myCell.Value = Target.Value Then
> myCell.Interior.ColorIndex = 4
> End If
> Next myCell
>
> End Sub
>
>
> Jonathan Brown wrote:
> >
> > I'm trying to make a script that will run when I edit a cell. Once I've
> > edited the cell I want the script to go to the top of the column and then
> > check to see if that value already exists anywhere in the target column. If
> > it does exist already then i want it to highlight it in red as well as the
> > cell that I just edited.
> >
> > It's going to be similar to the find duplicates in selected range
> > conditional formatting rule. But the selected range needs to be the target
> > column. If the same value exists in a different column then it shouldn't
> > even be concerned with it.
> >
> > Here's what I have so far. I'm trying to use some sort of loop to cycle
> > through each cell in the target column and compare it to the value of the
> > cell that I just changed.
> >
> > ----------------------------------------------------------------------------------------------
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >
> > tRow = Target.Row
> > Row = 1
> > tColumn = Target.Column
> >
> > Cell1 = Sh.Cells(tRow, tColumn).Value
> >
> > For Each cell In Columns(tColumn).Select
> >
> > Cell2 = Sh.Cells(Row, tColumn).Value
> >
> > If Cell1.Value = Cell2.Value Then
> >
> > '.PatternColorIndex = x1Automatic
> > Sh.Cells(Cell2).Interior.ColorIndex = 4
> >
> > End If
> >
> > Row = Row + 1
> >
> > Next
> >
> > End Sub
> > --------------------------------------------------------------------------------------------
> >
> > Can anyone help me out?
>
> --
>
> Dave Peterson
>

Did you test mine?

"Jonathan Brown" wrote:

> Dave,
>
> The procedure you provided is working for finding duplicates and changing
> the highlight color, but it'll still highlight if there's a duplicate on the
> row. All I want is for it to find a duplicate in the column. If there's two
> cells side by side with the same value then I don't want it to change the
> color. but if I have two cells above and below each other then I do want it
> to change it's color.
>
> could you help me with that?
>
>
> "Dave Peterson" wrote:
>
> > Option Explicit
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >
> > Dim HowMany As Long
> > Dim myCell As Range
> >
> > 'clear existing colors???
> > Target.EntireColumn.Interior.ColorIndex = xlNone
> >
> > If Target.Cells.Count > 1 Then
> > Exit Sub
> > End If
> >
> > If Target.Value = "" Then
> > Exit Sub
> > End If
> >
> > HowMany = Application.CountIf(Target.EntireColumn, Target.Value)
> >
> > If HowMany = 1 Then
> > 'ok, just the new value
> > Exit Sub
> > End If
> >
> > For Each myCell In Intersect(Sh.UsedRange, Target.EntireColumn)
> > If myCell.Value = Target.Value Then
> > myCell.Interior.ColorIndex = 4
> > End If
> > Next myCell
> >
> > End Sub
> >
> >
> > Jonathan Brown wrote:
> > >
> > > I'm trying to make a script that will run when I edit a cell. Once I've
> > > edited the cell I want the script to go to the top of the column and then
> > > check to see if that value already exists anywhere in the target column. If
> > > it does exist already then i want it to highlight it in red as well as the
> > > cell that I just edited.
> > >
> > > It's going to be similar to the find duplicates in selected range
> > > conditional formatting rule. But the selected range needs to be the target
> > > column. If the same value exists in a different column then it shouldn't
> > > even be concerned with it.
> > >
> > > Here's what I have so far. I'm trying to use some sort of loop to cycle
> > > through each cell in the target column and compare it to the value of the
> > > cell that I just changed.
> > >
> > > ----------------------------------------------------------------------------------------------
> > > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> > >
> > > tRow = Target.Row
> > > Row = 1
> > > tColumn = Target.Column
> > >
> > > Cell1 = Sh.Cells(tRow, tColumn).Value
> > >
> > > For Each cell In Columns(tColumn).Select
> > >
> > > Cell2 = Sh.Cells(Row, tColumn).Value
> > >
> > > If Cell1.Value = Cell2.Value Then
> > >
> > > '.PatternColorIndex = x1Automatic
> > > Sh.Cells(Cell2).Interior.ColorIndex = 4
> > >
> > > End If
> > >
> > > Row = Row + 1
> > >
> > > Next
> > >
> > > End Sub
> > > --------------------------------------------------------------------------------------------
> > >
> > > Can anyone help me out?
> >
> > --
> >
> > Dave Peterson
> >