Using For-loop to establish sequential variables?

[CV355]

I am using a user form to pull cell colors to allow a user to set custom colors for reporting.

I thought I could use a for loop to set these sequentially but it doesn't seem to work. Is there a cleaner way of doing this?

"Aspect_" & i & "_Col" = Cells(7,14 + i).Interior.Color does not work

Aspect_1_Col = Cells(7, 15).Interior.Color
Aspect_2_Col = Cells(7, 16).Interior.Color
Aspect_3_Col = Cells(7, 17).Interior.Color
Aspect_4_Col = Cells(7, 18).Interior.Color
Aspect_5_Col = Cells(7, 19).Interior.Color
Aspect_6_Col = Cells(7, 20).Interior.Color
Aspect_7_Col = Cells(7, 21).Interior.Color
Aspect_8_Col = Cells(7, 22).Interior.Color
Aspect_9_Col = Cells(7, 23).Interior.Color
Aspect_10_Col = Cells(7, 24).Interior.Color
Aspect_11_Col = Cells(7, 25).Interior.Color

Similarly, is there a solution for establishing these variables in a cleaner fashion than listing them all out individually?

Thank you in advance.

 

[Fluff]

One option would be to use an array

   Dim Aspect_Col(1 To 11) As Variant
   Dim i As Long
   
   For i = 1 To 11
      Aspect_Col(i) = Cells(7, i + 14).Interior.Color
   Next i

 

[CV355]

Thank you!
I don't know why I didn't think to do that initially!!

 

[Fluff]

Glad to help & thanks for the feedback

 

[MickG]

Not sure exactly what you want to do with the code, but if you have a Userform with a Combobox you can load all the colours into it and, on selection of the appropriate colour , range "A1" is changed to that colour.
The combobox shows both colour and variable relating to it. If you limit the combobox to one column , just the colour variables will show.
Load this code into your Userform Module (at top)

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
Range("A1").Interior.Color = Dic(ComboBox1.Value)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("O7:Y7")
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Dic("Aspect_" & Dic.Count & "_Col") = Dn.Interior.Color
    [COLOR="Navy"]Next[/COLOR]
ComboBox1.ColumnCount = 2
Me.ComboBox1.List = Application.Transpose(Array(Dic.Keys, Dic.items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick