Using variables for vb[color]

[Novice_excel]

Greetings everyone,

Can't say I contribute a whole lot to this community but I sure appreciate the information provided here.

I have some problem using a variable in place of the following code;

    If HighLightResponse = vbYes Then
        
        If ColorResponse = Empty Then
            ColorResponse = InputBox(prompt:="Please pick color of highlight (vb+; Blue, Red, Green", _
               Title:="Highlight Color")
        End If
        
        MsgBox "vb" & ColorResponse
        
        'Rows(RowNum).Font.Color = vbRed
        Rows(RowNum).Font.Color = ColorResponse
    End If

For some reason, vb does not recognize the ColorResponse variable. When I do run the code, I get the type mismatch error.

It is dim as string and it will work if the value was hardcoded into ColorResponse (ColorResponse = vbRed) but it will not work if the data came from the input box.

Someone help

 

[VoG]

No, you cannot manipulate variables or constants like that. what are you actually trying to do>

 

[mikerickson]

In that code the variable colorResponse is a string

The .Color property of a range is a number. not a string.

Cocanenating strings will not create variable names that VBA will recognize.

You'd need code like this. (Note the use of the ColorIndex property rahther than Color

Dim ColorResponse as String
Dim myColorIndex as Long

ColorResponse = InputBox("Input a color")
If ColorResponse = vbNullString Then Exit Sub: Rem canceled

Select Case LCase(ColorResponse)
    Case "red"
        myColorIndex = vbRed
    Case "blue"
        myColorIndex = vbBlue
    Case "green"
        myColorIndex = vbGreen
    Rem etc
End Select

If myColorIndex = 0 then Exit Sub: Rem bad entry e.g. "fish"

Rows(1).ColorIndex = myColorIndex

 

[Novice_excel]

This macro is supposed to give the user a choice of colors for use when it finds the desired string.

I wanted to use the input box option to allow the user to enter 'red' or 'blue' and the macro automatically uses that color scheme.

I hope I explained it correctly

 

[Novice_excel]

Mike,

Thank you very much. It worked beautifully.