Set RGB color from a value in a cell in Excel

Question

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

Siddharth Rout · Accepted Answer · 2017-06-09T08:24:26.650

2

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) _
                     )

edited Jun 09 '17 at 08:24

answered Jun 09 '17 at 08:06

Siddharth Rout

147,039
17
206
250

Way1 looks more convenient, however it leads to an error: `Argument not optional`. Way2 works fine! – AK47 Jun 09 '17 at 08:20
Yeah my fault. Apologies. Updated the post – Siddharth Rout Jun 09 '17 at 08:24

score 2 · Answer 2 · answered Jun 09 '17 at 08:24

2

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))

answered Jun 09 '17 at 08:24

L42

19,427
11
44
68

++ another nice approach :) – Siddharth Rout Jun 09 '17 at 08:37

Set RGB color from a value in a cell in Excel

2 Answers2