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