How to select a group of non-adjacent cells from cell addresses stored in an array?

Question

I have an 1 dimensional array of cell addresses (cells may be non-adjacent). I want to select all of these cells in order to change the interior color. The cells in the array represent all the cells in the workbook that have changed since the last version of the worksheet, and I need to highlight them in order to view the changes.

I tried converting the array to a string and then selecting them with the following code:

For index = 1 To UBound(RTemp)
    RTempStr = RTempStr & CStr(RTemp(index))
Next

RTempStr = Left(RTempStr, Len(RTempStr) - 1)

Worksheets("R1").Select
Union(RTempStr).Select
Selection.Interior = vbRed

RTemp is my array of cell addresses

The Union function returns a Type mismatch error

Any suggestions how to fix this? or alternate ways to select the cells?

Answer 1

Just create the Address by Joining:

Sub jumble()
    Dim ary(1 To 3) As String
    Dim s As String
    Dim r As Range
    ary(1) = "A1"
    ary(2) = "B9"
    ary(3) = "C11"
    s = Join(ary, ",")
    Set r = Range(s)
    r.Select
End Sub

Answer 2

Declare a range object and assign the non contiguous range to it. You can then directly change the interior color. See this example.

Sub sample()
    Dim rng As Range

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A2,C4:C7,D11:D12")

    rng.Interior.ColorIndex = 3
End Sub

I am hard coding "A1:A2,C4:C7,D11:D12" If you have the cell addresses in an array then you can construct that string and pass it to the range object. For example

Sub sample()
    Dim Ar(1 To 3) As String
    Dim rng As Range
    Dim strAddress As String
    Dim i As Long

    Ar(1) = "A1:A2"
    Ar(2) = "C4:C7"
    Ar(3) = "D11:D12"

    For i = 1 To 3
        If strAddress = "" Then
            strAddress = Ar(i)
        Else
            strAddress = strAddress & "," & Ar(i)
        End If
    Next

    Set rng = ThisWorkbook.Sheets("Sheet1").Range(strAddress)

    rng.Interior.ColorIndex = 3
End Sub

Followup from comments

If the array has say Cell(4,5) then use the function that I created below.

Option Explicit

Sub Sample()
    Dim Ar(1 To 5) As String
    Dim rng As Range
    Dim strAddress As String
    Dim i As Long

    Ar(1) = "Cells(4,5)"
    Ar(2) = "Cells(6,6)"
    Ar(3) = "Cells(7,7)"
    Ar(4) = "Cells(8,12)"
    Ar(5) = "Cells(9,12)"

    strAddress = Join(Ar, "#") '<~~ `Join` Courtsey Gary's Student
    strAddress = GetAddress(strAddress)

    Debug.Print strAddress

    Set rng = ThisWorkbook.Sheets("Sheet1").Range(strAddress)

    rng.Interior.ColorIndex = 3
End Sub

Function GetAddress(sAddr As String) As String
    Dim MyAr
    Dim sTemp As String, sAddrs As Range
    Dim Rw As Long, Col As Long, i As Long

    MyAr = Split(sAddr, "#")

    For i = 0 To UBound(MyAr)
        sTemp = Replace(Replace(MyAr(i), "Cells(", ""), ")", "")
        Rw = Val(Trim(Split(sTemp, ",")(0)))
        Col = Val(Trim(Split(sTemp, ",")(1)))


        If sAddrs Is Nothing Then
            Set sAddrs = ThisWorkbook.Sheets(1).Cells(Rw, Col)
        Else
            Set sAddrs = Union(sAddrs, ThisWorkbook.Sheets(1).Cells(Rw, Col))
        End If
    Next i

    GetAddress = sAddrs.Address
End Function