Applying VbColor Properties in a dynamic way

Question

I'm trying to apply the VbColor properties in a dynamic way. I have kept the basic colour in one dropdown list in Excel (Blue, Red, Yellow etc.,) and I have tried the following code to apply the colour based on the value selected in the list. But I end up getting an error (Type Mismatch - Runtime error -13).

Dim Q3Sht As Worksheet
Dim Colours As Variant

Set Q3Sht = ThisWorkbook.Sheets("Q3 Sol")

If ActiveCell.Column > 9 Then
    MsgBox "Please select the Data within range", vbExclamation + vbOKOnly
    Exit Sub
End If

Colours = "vb" & Q3Sht.Range("K5").Value

If ActiveCell.Column > 1 Then
    ActiveCell.End(xlToLeft).Select
    Range(ActiveCell, ActiveCell.Offset(0, 8)).Select
    Selection.Interior.Color = Colours ' I'm getting error applying the colour
    'Q3Sht.Range("K5").Interior.Color = vbBlue
Else:
    Range(ActiveCell, ActiveCell.Offset(0, 8)).Select
    Selection.Interior.Color = "vb" & Q3Sht.Range("K5")

Please help!! Thank you in advance.

You are trying to pass a string to Color property when it actually accepts a Long value representing the color. (https://learn.microsoft.com/en-us/office/vba/api/excel.interior.color) vbBlue is a constant that stores the actual value of the color value. You have to make your own Select Statement to evaluate your dropdown input to get the relevant constant. — Raymond Wu, May 07 '21 at 12:34
As far as I know, nope but there's only so many color constants so shouldn't take long to do it. — Raymond Wu, May 07 '21 at 12:38
Side note - You should try to fully qualify your code and stop using Select (Read this: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Raymond Wu, May 07 '21 at 12:51

score 1 · Answer 1 · answered May 07 '21 at 12:44

Dim Colours as variant
Colours = "vb" & Sheet1.Range("K5").Value`     ' You are passing a string value here

Although you are declaring Colours as variant but you are passing a string to it. Hence the mismatch error. Instead you can make use of colorindex property

Colours = Sheet1.Range("K5").Value               'Populate K5 with corresonding colorindex like say 3 for vbred.
Range(ActiveCell, ActiveCell.Offset(0, 8)).Select
Selection.Interior.ColorIndex = Colours

score 1 · Accepted Answer · answered May 07 '21 at 12:48

Below is a sample of how you can evaluate the input to the relevant color constants:

Private Sub Test()   
    Dim Colours As Long
    
    Select Case Q3Sht.Range("K5").Value
        Case "Black": Colours = vbBlack
        Case "Red": Colours = vbRed
        Case "Green": Colours = vbGreen
        Case "Yellow": Colours = vbYellow
        Case "Blue": Colours = vbBlue
        Case "Magenta": Colours = vbMagenta
        Case "Cyan": Colours = vbCyan
        Case "White": Colours = vbWhite
    End Select
    
    Selection.Interior.Color = Colours
End Sub

Applying VbColor Properties in a dynamic way

2 Answers2