EXCEL VBA MACRO: Looping help

[ariesmethod]

Shown below is the code that I want looped. I want it to loop it to 5 different colors (RGB(197, 217, 241), RGB (0, 191, 288), etc) to make the coding look neater. Otherwise, I'd have to copy and paste the code 5 different times with different color codings. Note that there are two areas in bold below that has the same exact color coding. Any help would greatly be appreciated!

Application.FindFormat.Interior.Color = RGB(197, 217, 241)
If Not Range("A2:A10000").Find(What:="", SearchFormat:=True) Is Nothing Then
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$10000").AutoFilter Field:=1, Criteria1:=RGB(197, _
217, 241), Operator:=xlFilterCellColor
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("New Member").Select
If Range("A1").Value = "" Then
Range("A1").Select
ActiveSheet.Paste
Else
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Delete
End If
Sheets("All Members").Select
Selection.AutoFilter
End If

 

[RickXL]

Hi,

I have not tested this but it looks promising!

Sub xx()

    Dim ColorArray(1 To 5) As Long
    Dim i As Long
    
    ColorArray(1) = RGB(197, 217, 241)
    ColorArray(2) = RGB(0, 191, 288)
    ColorArray(3) = RGB(1, 1, 1)
    ColorArray(4) = RGB(11, 11, 11)
    ColorArray(5) = RGB(111, 111, 111)
    
    For i = 1 To 5
        Application.FindFormat.Interior.Color = ColorArray(i)
        If Not Range("A2:A10000").Find(What:="", SearchFormat:=True) Is Nothing Then
            Range("A1").Select
            Selection.AutoFilter
            ActiveSheet.Range("$A$1:$R$10000").AutoFilter Field:=1, Criteria1:=ColorArray(i), Operator:=xlFilterCellColor
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy
            Sheets("New Member").Select
            If Range("A1").Value = "" Then
                Range("A1").Select
                ActiveSheet.Paste
            Else
                Range("A1").Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(1, 0).Range("A1").Select
                ActiveSheet.Paste
                ActiveCell.Offset(0, 1).Range("A1").Select
                Selection.EntireRow.Delete
            End If
            Sheets("All Members").Select
            Selection.AutoFilter
        End If
    Next i

End Sub

I have relied on the fact that RGB(197, 217, 241) gets converted to a number, 15849925 in fact. The 5 RGB values are stored in an array then the loop can call up the array element number when it needs it.

 

[ariesmethod]

Hi,

I have not tested this but it looks promising!

Sub xx()

    Dim ColorArray(1 To 5) As Long
    Dim i As Long
    
    ColorArray(1) = RGB(197, 217, 241)
    ColorArray(2) = RGB(0, 191, 288)
    ColorArray(3) = RGB(1, 1, 1)
    ColorArray(4) = RGB(11, 11, 11)
    ColorArray(5) = RGB(111, 111, 111)
    
    For i = 1 To 5
        Application.FindFormat.Interior.Color = ColorArray(i)
        If Not Range("A2:A10000").Find(What:="", SearchFormat:=True) Is Nothing Then
            Range("A1").Select
            Selection.AutoFilter
            ActiveSheet.Range("$A$1:$R$10000").AutoFilter Field:=1, Criteria1:=ColorArray(i), Operator:=xlFilterCellColor
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy
            Sheets("New Member").Select
            If Range("A1").Value = "" Then
                Range("A1").Select
                ActiveSheet.Paste
            Else
                Range("A1").Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(1, 0).Range("A1").Select
                ActiveSheet.Paste
                ActiveCell.Offset(0, 1).Range("A1").Select
                Selection.EntireRow.Delete
            End If
            Sheets("All Members").Select
            Selection.AutoFilter
        End If
    Next i

End Sub

I have relied on the fact that RGB(197, 217, 241) gets converted to a number, 15849925 in fact. The 5 RGB values are stored in an array then the loop can call up the array element number when it needs it.

Awesome! This works perfectly! Thanks a lot. This got me to learn how arrays work on VBA as well.

 

[RickXL]

Good, I am pleased it was useful.

And thanks for the feedback.