Can't set Font.Color to RGB value when it is stored in a variable

[tpturner]

First time post. Please excuse any indiscretions.

' I want to set Selection.Font.Color using RGB values, which are stored in a cell in a different worksheet ( e.g., RGB(0, 102, 204) )
' Here is some of the code

Dim DataWorksheet as String
Dim SelectionFontColorCell As String
Dim SelectionFontColor As String
Dim TeamColumn As String


DataWorksheet = "International"
SelectionFontColorCell = TeamColumn & "42" ' contains the text "RGB(0, 102, 204)"
SelectionFontColor = Range(DataWorksheet & "!" & SelectionFontColorCell).Value
' SelectionFontColor is now "RGB(0, 102, 204)", without the quotes
' "RGB(0, 102, 204)" is the same as Hexadecimal "&HCC6600"

' Change font color for the Name I am writing to a particular cell
Selection.Font.Color = SelectionFontColor ' This fails
SelectionFontColor = RGB(0, 102, 204) ' This works


' If SelectionFontColorCell contains the text "&HCC6600", then the following works:
Selection.Font.Color = SelectionFontColor ' This works


The error I receive is "Run time error (13): Type mismatch".
I don't know how to resolve the issue.

Any/all help is appreciated.

Many thanks!

 

[tpturner]

Well, I have figured out a workaround. Thanks in advance to anyone who already put in time. I am still interested in finding out why it did not work or finding out a better solution.

This works:

The user enters an RGB value, such as "(RGB(0, 102, 204)", but without the quotes. This is stored in a cell in a different worksheet and retrieved. My I go from there.

' Try to correct for mistakes that the user may have done when entering the RGB value in the database.
' Remove any spaces
SelectionFontColor = Replace(SelectionFontColor, " ", "")

' Remove the "RGB"
SelectionFontColor = Replace(SelectionFontColor, "RGB", "")


' Remove the parens: "(" and ")"
SelectionFontColor = Replace(SelectionFontColor, "(", "")
SelectionFontColor = Replace(SelectionFontColor, ")", "")


' What remains should look something like this, without the quotes: "0,102,204"

' Split it into Red, Green and Blue numbers
Dim FirstComma As Integer
Dim myRed As String
Dim myGreen As String
Dim myBlue As String

' Determine the RED
FirstComma = (InStr(SelectionFontColor, ","))
myRed = Left(SelectionFontColor, FirstComma - 1)

' Delete the characters up to the first comma
SelectionFontColor = Mid(SelectionFontColor, FirstComma + 1)

' Determine the GREEN
FirstComma = (InStr(SelectionFontColor, ","))
myGreen = Left(SelectionFontColor, FirstComma - 1)

' Delete the characters up to the first comma
SelectionFontColor = Mid(SelectionFontColor, FirstComma + 1)

' BLUE is what remains
myBlue = SelectionFontColor

' Need to reverse the colors for VBA to provide the proper color
' So RGB becomes BGR
' Put "&H" in front of it all
SelectionFontColor = "&H" & Hex(myBlue) & Hex(myGreen) & Hex(myRed)

Selection.Font.Color = SelectionFontColor ' This works!!!

 

[tpturner]

Well, I was incorrect. My second post does not work all the time,
because I did not declare the colors to be BYTE. I used STRING, which was incorrect.


Now, as far as I have tested, the code below does work properly,
although it is not the most efficient or elegant:

Sorry for all the posts.


Situation:
The user enters an RGB value,
such as "RGB(0, 102, 204)", but without the quotes.
This is stored in a cell in a different worksheet and retrieved via VBA code.


Dim SelectionFontColor As String
Dim DefaultFontColor As String


' Change font color for the Team Name and the Players to be a Blue
' Example: Selection.Font.Color = RGB(0, 102, 204)
' RGB(0, 102, 204) is the same as Hexadecimal 0066CC.
' But VBA needs it reversed, so it needs to be CC6600.


' Try to correct for mistakes that the user may have done
' when entering the RGB value in the database.
' First, retrieve the value
SelectionFontColor = Range(DataWorksheet & "!" & SelectionFontColorCell).Value
' MsgBox ("Starting as: " & SelectionFontColor)
' Remove any spaces
SelectionFontColor = Replace(SelectionFontColor, " ", "")

' If empty, then use the default font color
If Len(SelectionFontColor) = 0 Then SelectionFontColor = DefaultFontColor


' Remove the "RGB" or "rgb"
SelectionFontColor = Replace(SelectionFontColor, "RGB", "")
SelectionFontColor = Replace(SelectionFontColor, "rgb", "")


' Remove the parens: "(" and ")"
SelectionFontColor = Replace(SelectionFontColor, "(", "")
SelectionFontColor = Replace(SelectionFontColor, ")", "")


' What remains should look something like this, without the quotes: "0,102,204"
' MsgBox ("Replacements: " & SelectionFontColor)

' Split it into Red, Green and Blue numbers
Dim FirstComma As Integer

Dim myRed As Byte
Dim myGreen As Byte
Dim myBlue As Byte

Dim myRed2 As String
Dim myGreen2 As String
Dim myBlue2 As String



' Determine the RED
FirstComma = (InStr(SelectionFontColor, ","))
myRed = Left(SelectionFontColor, FirstComma - 1)
If myRed < 16 Then
myRed2 = 0 & Hex(myRed)
Else
myRed2 = Hex(myRed)
End If
' MsgBox ("Red: " & myRed & " Red2: " & myRed2)

' Delete the characters up to the first comma
SelectionFontColor = Mid(SelectionFontColor, FirstComma + 1)



' Determine the GREEN
FirstComma = (InStr(SelectionFontColor, ","))
myGreen = Left(SelectionFontColor, FirstComma - 1)
If myGreen < 16 Then
myGreen2 = 0 & Hex(myGreen)
Else
myGreen2 = Hex(myGreen)
End If
' MsgBox ("Green: " & myGreen & " Green2: " & myGreen2)

' Delete the characters up to the first comma
SelectionFontColor = Mid(SelectionFontColor, FirstComma + 1)



' BLUE is what remains
myBlue = SelectionFontColor
If myBlue < 16 Then
myBlue2 = 0 & Hex(myBlue)
Else
myBlue2 = Hex(myBlue)
End If
' MsgBox ("Blue: " & myBlue & " Blue2: " & myBlue2)

' Need to reverse the colors for VBA to provide the proper color
' So RGB becomes BGR
' Put "&H" in front of it all
SelectionFontColor = "&H" & myBlue2 & myGreen2 & myRed2
' MsgBox ("SelectionFontColor: " & SelectionFontColor)

Selection.Font.Color = SelectionFontColor