Change Cell Background Colour from predefined RGB Colour List each time Macro is run-CodePudding

I'm trying to create a Macro where a range of cells Background colour is changed to a colour in a list of predefined RGB colours. Each time the macro is run the Range of cells colour changes to the next colour in the list.

I have a Reference number in cell "A9" that sets the number in the list of colours (defined as colourSel), and a list of 5 RGB colours underneath it (A10:A14). When the Last colour has been selected, the colourSel Variable resets and the list begins form the start again. I can cycle through the colours fine but I'm unable to set the Colour Variable (colourSet) using Interior.Color

A9 3 (ColourSel) A10 RGB(131, 99, 172) A11 RGB(240, 92, 106) A12 RGB(13, 176, 219) A13 RGB(231, 167, 35) A14 RGB(244, 130, 33)

I'm getting the error Type mismatch which I assume is because colourSet is defined as String

Dim colourSel As Integer
Dim colourSet As String

Range("a9").Select
If ActiveCell.Value > 5 Then
    colourSel = 1
    GoTo resetcolour
    End If

colourSel = Range("a9").Value

resetcolour:

Range("a9").Offset(colourSel, 0).Select
colourSet = ActiveCell.Value

colourSel = colourSel   1
    Range("a9").Value = colourSel

    Range("b2:c3").Interior.Color = colourSet

CodePudding user response：

You can use this function within your VBA to perform the conversion ...

Public Function ConvertRGBStringToValues(strRGB As String)
    Dim arrRGB As Variant

    ' This is an example of what can be passed into this function ...
    ' strRGB = "RGB(11, 105, 11)"
    
    arrRGB = Split(Replace(Replace(Replace(strRGB, "RGB", "", Compare:=vbTextCompare), ")", ""), "(", ""), ",")
    
    ConvertRGBStringToValues = RGB(arrRGB(0), arrRGB(1), arrRGB(2))
End Function

... so you would apply it like this ...

Range("b2:c3").Interior.Color = ConvertRGBStringToValues(colourSet)