CODEWITHSUNDEEP
vbaexcelcolors
Michal
Michal

Reputation: 19

How to transform cell string value RGB into color number in Excel VBA?

I have cells that contain color values ie.:

RGB(0, 128, 0)

trying to use this cell.value in VBA to color my cells but my color is not recognized. It shows as string and it does not work

"RGB(0, 128, 0)"

How to transform this text or remove " " so my color function will work correctly?

While Not Worksheets("Sheet3").Range("A" & j).Value = ""
    color = Sheets("Sheet3").Cells(j, 2).Value
    Sheets("Sheet3").Cells(j, 4).Interior.color = color
    j = j + 1
Wend

Upvotes: 0

Views: 2408

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12665

You cannot interpret strings as code variables/functions.

In other words, you can't expect to write this in a string: 

str = "a = a + 2"

... and expect that your code reads it as: 

a = a + 2

However, what you can do is splitting the three numbers from the string and using them in your code: 

color = Sheets("Sheet3").Cells(j, 2).Value
Sheets("Sheet3").Cells(j,4).Interior.Color = RGB(Replace(Split(color,",")(0),"RGB(",""), Split(color,",")(1), Replace(Split(color,",")(2),")","")

If you always have the same pattern (like RGB(0,10,20) written in a cell), you can think of creating a custom function which returns the three numbers: 

Private Function splitRGB(ByVal vl As String) As Integer()
    splitRGB = yourArrayWithTheThreeValues '(split as above)
End Function

Upvotes: 1

Related Questions