J
Jonathan Brown
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
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