CODEWITHSUNDEEP
vbaexcelcolorsrgb
AK47
AK47

Reputation: 1328

Set RGB color from a value in a cell in Excel

I have a macro which draw bunch of charts for me. What I'd like to do is dynamically change color of Data series. I use RGB color palete and function .ForeColor.RGB. When I use it directly .ForeColor.RGB = RGB(88, 88, 88) - everything works fine. But When I try to take color from a cell .ForeColor.RGB = Sheets(1).Cells(1, 1) I got an error Type missmatch. In Cell(1,1) I have value: RGB(0, 0, 0).

How to pick color for Data series from cell?

Macro and Excel window below.

Sub Macro1()

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).Select

MsgBox Sheets(1).Cells(1, 1)

With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = Sheets(1).Cells(1, 1)
    '.ForeColor.RGB = RGB(88, 88, 88)
    .Transparency = 0
    .Solid
End With

End Sub

enter image description here

Upvotes: 1

Views: 1963

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

You can't pass function name as a String value. RGB() is a Function

Try this. This will take values from 3 different cells say D4,E4 and F4.

.ForeColor.RGB = RGB( _
                     Sheets(1).Cells(4, 4), _
                     Sheets(1).Cells(4, 5), _
                     Sheets(1).Cells(4, 6) _
                     )

Upvotes: 2

L42
L42

Reputation: 19727

Try this:

'/* have to add this line */
ret = Split(Replace(Replace(Sheets(1).Cells(1, 1), "RGB(", ""), ")", ""), ",")
.ForeColor.RGB = RGB(ret(0),ret(1),ret(2))

Upvotes: 2

Related Questions