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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
Named argument not found, error for the following line in the code:
Set r = Columns("c").Find("", seachformat:=True)
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
Jindon, I get the same error for:
Set r = Columns("c").Find(What:="", LookAt:=xlWhole, SeachFormat:=True)
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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?
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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.
 

Forum statistics

Threads
1,148,371
Messages
5,746,308
Members
424,006
Latest member
Metal_warrior

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
Top