Updating Code1

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
Hello, how do I update the following code:

This one only delete the Yellow Combos when 2 number within the combos are the same and not all different:
example: 006, 606 [DELETE], 312 [NO ACTION]
Code:
Sub DeleteYellowCombos()
 For i = 5 To 804
  If Cells(i, "C") <> Cells(i, "D") And Cells(i, "C") <> Cells(i, "E") And Cells(i, "D") <> Cells(i, "E") Then
    If Abs(Cells(i, "C").Interior.ColorIndex = 6) + Abs(Cells(i, "D").Interior.ColorIndex = 6) + Abs(Cells(i, "E").Interior.ColorIndex = 6) > 1 Then
      Cells(i, "C").Resize(, 3).ClearContents
    End If
  End If
 Next i
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Code:
Sub Sample()
Dim r As Range, ff As String
Application.FindFormat.Interior.ColorIndex = 3
Set r = Columns("c").Find("",seachformat:=True)
If Not r Is Nothing Then
     ff = r.Address
     Do
          If (r.Value <> r.Offset(,1).Value) * (r.Value <> r.Offset(,2).Value) * _
             (r.Offset(,1).Value <> r.Offset(,2).Value) Then
             r.Resize(,3).ClearContents
          End If
     Set r = Columns("c").Find("",r,searchformat:=True)
      Loop Until ff = r.Address
End If
End Sub
 
Upvote 0
Named argument not found, error for the following line in the code:
Set r = Columns("c").Find("", seachformat:=True)
 
Upvote 0
Code:
Sub Sample()
Dim r As Range, ff As String
Application.FindFormat.Interior.ColorIndex = 3
Set r = Columns("c").Find(What:="",LookAt:=xlwhole,SeachFormat:=True)
If Not r Is Nothing Then
     ff = r.Address
     Do
          If (r.Value <> r.Offset(,1).Value) * (r.Value <> r.Offset(,2).Value) * _
             (r.Offset(,1).Value <> r.Offset(,2).Value) Then
             r.Resize(,3).ClearContents
          End If
     Set r = Columns("c").Find(What:="",After:=r,LookAt:=xlWhole,SearchFormat:=True)
      Loop Until ff = r.Address
End If
End Sub
 
Upvote 0
Jindon, I get the same error for:
Set r = Columns("c").Find(What:="", LookAt:=xlWhole, SeachFormat:=True)
 
Upvote 0
Hello, how do I update the following code:

This one only delete the Yellow Combos when 2 number within the combos are the same and not all different:
example: 006, 606 [DELETE], 312 [NO ACTION]
Code:
Sub DeleteYellowCombos()
 For i = 5 To 804
  If Cells(i, "C") <> Cells(i, "D") And Cells(i, "C") <> Cells(i, "E") And Cells(i, "D") <> Cells(i, "E") Then
    If Abs(Cells(i, "C").Interior.ColorIndex = 6) + Abs(Cells(i, "D").Interior.ColorIndex = 6) + Abs(Cells(i, "E").Interior.ColorIndex = 6) > 1 Then
      Cells(i, "C").Resize(, 3).ClearContents
    End If
  End If
 Next i
End Sub
dk
BTW, how do you want to update this code?
 
Upvote 0
The original code delete all combos in the range with yellow background that have all different values like 123, 456, 789, now I do not want it to delete all different values, I want it to delete values that have 2 or 3 numbers within the combination which are the same, like 113, 338, 455

Also, only 2 of the 3 numbers in the combo will have a yellow background.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top