Sorting cells by colour and putting their address into an Array

I want to put the values from cells with background colour that are different from -4142, in an array so that later I can insert those values in a dropdown list.

I found code in the answer of this question: Appending a dynamic array in VBA.

I get

error 13 incompatible types

in the line myArray(X) = cell.Address

I don't know if the message is that in English because my VBA is in another language, but the error number is 13.

Sub SelectByColor()
Dim cell As Range
Dim rng As Range
Dim LR As Long
Dim myArray() As Double, X As Long
X = 0
'For understanding LR = Last Row
LR = Range("B:B").SpecialCells(xlCellTypeLastCell).Row

ReDim Preserve myArray(X)
Set rng = Range("B2:B" & LR)
For Each cell In rng.Cells
    If cell.Interior.ColorIndex <> -4142 Then
        myArray(X) = cell.Address
        X = X + 1
        If X < N Then ReDim Preserve myArray(0 To X)
        mystr = mystr & cell.Address & ","
    End If
Next cell
mystr = Left(mystr, Len(mystr) - 1)
MsgBox mystr
MsgBox myArray(X)

End Sub

mystr is to see if the code would be getting the correct values, and it is, but it is not appending in the array.

Solution

(a) You get your runtime error because you declares your array to hold numbers, but you are trying to write addresses (=strings) into it. An cell address (eg $A$1) cannot be converted into a number and therefore VBA throws that error 13.

(b) A list of values used as Data Validation can be created by a range of cells or by a (hardcoded) list of values. However, the range need to be contiguous, which is not the case for your requirements.

So what you need is a list of values. The values need to be separated by ",". You can do the by creating an array as you do in your code and then use the Join-function. However, the array needs to be of type String or Variant, it will not work with Double.

As I don't like to use Redim Preserve in a Loop (very inefficient), I changed the logic by sizing the array with the maximum of possible values (LR) and then use only a single Redim to remove unused entries after the values are filled.

ReDim myArray(LR)
Dim X As Long, rng as Range, cell as Range
Set rng = ActiveSheet.Range("B2:B" & LR)
For Each cell In rng.Cells
    If cell.Interior.ColorIndex <> -4142 and not isError(cell.value) Then
        myArray(X) = cell.Value
        X = X + 1
    End If
Next cell
Redim Preserve myArray(X)    ' Remove unused entries

Set the validiation:

 With Selection.Validation  ' <-- Replace Selection with the Range where you want to apply the validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Join(myArray, ",")
 End With