CODEWITHSUNDEEP
vbaexcelrgb
Monic
Monic

Reputation: 746

VBA Excel - Set row background depends on cell value

I have such rows in Excel:

Subject     VariableName    VariableValue
Color       vColor_Chart1   RGB(217,217,217)
Color       vColor_Chart2   RGB(210,110,42)

I want to create macro to change row background depens on cell value in VariableValue column.

I have such code for now:

Sub SetBackground()

Dim rngRange As Range
Dim rngRow As Range
Dim rgbCell As Range

Set rngRange = Range("A2:K13")
For Each rngRow In rngRange.Rows
    Set rgbCell = Range("E" & rngRow.Row) ' E it is column of VariableValue in my sheet
    rngRow.Interior.Color = rgbCell.Value 'here it doesn't works
Next

End Sub

And I don't know how to 'run' RGB function from cell.value.

The error from rngRow.Interior.Color = rgbCell row:

Run-time error '13':
Type mismatch

Upvotes: 1

Views: 789

Answers (2)

leekaiinthesky
leekaiinthesky

Reputation: 5593

You have to set rngRow.Interior.Color to an actual color object, but you're currently setting it to a string. If you change this line in your code:

rngRow.Interior.Color = rgbCell.Value 'here it doesn't works

To this:

If Left(rgbCell.Value, 4) = "RGB(" Then
    rgbValues = Split(Mid(rgbCell.Value, 5, Len(rgbCell.Value) - 5), ",")
    rngRow.Interior.Color = RGB(rgbValues(0), rgbValues(1), rgbValues(2))
End If

Then that will build the color object from the numbers in the string.

Upvotes: 0

user4039065
user4039065

Reputation:

The RGB Function (Visual Basic) is a VBA function that builds a color constant from three integers. You cannot use it by passing in a text string that looks like a fully formed function call.

If you are absolutely determined to have the formula as text in the cell, a little manipulation to evaluate the formula from its text string should be sufficient.

Dim sRGB As String, r As Integer, g As Integer, b As Integer
sRGB = rgbCell.Value    'example: "RGB(210,110,42)"
r = Int(Split(Split(sRGB, ",")(0), "(")(1))
g = Int(Split(sRGB, ",")(1))
b = Int(Split(Split(sRGB, ",")(2), ")")(0))
'Debug.Print RGB(r, g, b)
rngRow.Interior.Color = RGB(r, g, b) 'here it works

Upvotes: 1

Related Questions