| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't figure out how to do this loop
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
"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 ----------------------------------------------------------------------------------------------- |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
I only look through the single column.
Did you try my version or did you make changes? If you have other cells that are shaded, it's not because of the code I suggested. 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 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
Dave,
After fiddling around with your code I'm really liking it. I would like to change it a little bit so that if I fix one of the two duplicate cells it'll put both of the two cells that were duplicates back to white. This is assuming that there are only two duplicates. I guess if there's more than two duplicates then it'll just change the one that I fix back to white and leave the other duplicates in red. I've got it almost working. Here's what I've got after fiddling with your code: ---------------------------------------------------------------------------------------- Private Sub Worksheet_Change(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 If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = xlNone 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(ActiveSheet.UsedRange, Target.EntireColumn) ' If Target.Interior.ColorIndex = 3 Then ' Target.Interior.ColorIndex = xlNone ' End If ' ' If myCell.Interior.ColorIndex = 3 Then ' myCell.Interior.ColorIndex = xlNone ' End If If myCell.Value = Target.Value Then myCell.Interior.ColorIndex = 3 ElseIf myCell.Value < Target.Value Then If myCell.Interior.ColorIndex = 3 Or Target.Interior.ColorIndex = 3 Then myCell.Interior.ColorIndex = xlNone Target.Interior.ColorIndex = xlNone End If End If Next myCell End Sub ---------------------------------------------------------------------------------------------- By the way, thanks for your help, I really appreciate it. "Dave Peterson" wrote: I only look through the single column. Did you try my version or did you make changes? If you have other cells that are shaded, it's not because of the code I suggested. 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 -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't figure out how to do this loop
Before you spend anymore time using code, is there a reason that you don't want
to use format|conditional formatting. You can make sure that all the duplicates are shaded--no matter what's changed. Select the range (say the entire columns A:X) Then with A1 the Activecell Format|conditional Formatting Formula is: =COUNTIF(A:A,A1)1 (and give it a nice pattern format) ==== Just as an aside, I wouldn't use the entire column--just use the rows that you want to use (plus a few more for insurance!): Say you select A1:X99 and with A1 the activecell format|Conditional format Formula is: =COUNTIF(A$1:A$99,A1)1 Jonathan Brown wrote: Dave, After fiddling around with your code I'm really liking it. I would like to change it a little bit so that if I fix one of the two duplicate cells it'll put both of the two cells that were duplicates back to white. This is assuming that there are only two duplicates. I guess if there's more than two duplicates then it'll just change the one that I fix back to white and leave the other duplicates in red. I've got it almost working. Here's what I've got after fiddling with your code: ---------------------------------------------------------------------------------------- Private Sub Worksheet_Change(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 If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = xlNone 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(ActiveSheet.UsedRange, Target.EntireColumn) ' If Target.Interior.ColorIndex = 3 Then ' Target.Interior.ColorIndex = xlNone ' End If ' ' If myCell.Interior.ColorIndex = 3 Then ' myCell.Interior.ColorIndex = xlNone ' End If If myCell.Value = Target.Value Then myCell.Interior.ColorIndex = 3 ElseIf myCell.Value < Target.Value Then If myCell.Interior.ColorIndex = 3 Or Target.Interior.ColorIndex = 3 Then myCell.Interior.ColorIndex = xlNone Target.Interior.ColorIndex = xlNone End If End If Next myCell End Sub ---------------------------------------------------------------------------------------------- By the way, thanks for your help, I really appreciate it. "Dave Peterson" wrote: I only look through the single column. Did you try my version or did you make changes? If you have other cells that are shaded, it's not because of the code I suggested. 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HOW TO 'BLINK' A FIGURE INSTEAD OFF BOLDING THE FIGURE | Excel Worksheet Functions | |||
| returning back to loop check condition without completing the loop | Excel Programming | |||
| Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
| (Complex) Loop within loop to create worksheets | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Hybrid Mode
