Changing graph rows by reference to a cell

[Red over White]

I have a line graph that can have a variable number (currently 31) of lines in it, some of which will be same colour. The macro currently reads:

Sub InterestG1()

Sheets("InterestG").Activate

With ActiveChart

' 1 - 5

.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(192, 192, 192) ' 25% Grey - B25
.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 0, 255) ' Magenta - C25
.SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(210, 180, 140) ' Tan - D25 and so on
.SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(255, 255, 0) ' Yellow
.SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(51, 102, 155) ' Aqua

' 6 - 10

.SeriesCollection(6).Format.Line.ForeColor.RGB = RGB(144, 238, 144) ' Light Green
.SeriesCollection(7).Format.Line.ForeColor.RGB = RGB(144, 238, 144) ' Light Green
.SeriesCollection(8).Format.Line.ForeColor.RGB = RGB(0, 128, 128) ' Teal
.SeriesCollection(9).Format.Line.ForeColor.RGB = RGB(51, 102, 155) ' Aqua
.SeriesCollection(10).Format.Line.ForeColor.RGB = RGB(0, 65, 194) ' Blueberry

' 11- 15

.SeriesCollection(11).Format.Line.ForeColor.RGB = RGB(153, 204, 155) ' Aluminium
.SeriesCollection(12).Format.Line.ForeColor.RGB = RGB(51, 102, 155) ' Aqua
.SeriesCollection(13).Format.Line.ForeColor.RGB = RGB(153, 204, 155) ' Aluminium
.SeriesCollection(14).Format.Line.ForeColor.RGB = RGB(128, 0, 64) ' Maroon
.SeriesCollection(15).Format.Line.ForeColor.RGB = RGB(102, 204, 255) ' Sky

' 16 - 20

.SeriesCollection(16).Format.Line.ForeColor.RGB = RGB(102, 204, 255) ' Sky
.SeriesCollection(17).Format.Line.ForeColor.RGB = RGB(255, 111, 207) ' Carnation
.SeriesCollection(18).Format.Line.ForeColor.RGB = RGB(255, 67, 164) ' Strawberry
.SeriesCollection(19).Format.Line.ForeColor.RGB = RGB(0, 128, 0) ' Spring
.SeriesCollection(20).Format.Line.ForeColor.RGB = RGB(242, 133, 0) ' Tangerine

' 21 - 25

.SeriesCollection(21).Format.Line.ForeColor.RGB = RGB(255, 0, 255) ' Magenta
.SeriesCollection(22).Format.Line.ForeColor.RGB = RGB(242, 133, 0) ' Tangerine
.SeriesCollection(23).Format.Line.ForeColor.RGB = RGB(210, 180, 140) ' Tan
.SeriesCollection(24).Format.Line.ForeColor.RGB = RGB(242, 133, 0) ' Tangerine
.SeriesCollection(25).Format.Line.ForeColor.RGB = RGB(128, 64, 0) ' Mocha

' The rest

.SeriesCollection(26).Format.Line.ForeColor.RGB = RGB(128, 0, 64) ' Maroon
.SeriesCollection(27).Format.Line.ForeColor.RGB = RGB(0, 65, 194) ' Blueberry
.SeriesCollection(28).Format.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red
.SeriesCollection(29).Format.Line.ForeColor.RGB = RGB(153, 204, 155) ' Aluminium
.SeriesCollection(30).Format.Line.ForeColor.RGB = RGB(255, 165, 0) ' Orange
.SeriesCollection(31).Format.Line.ForeColor.RGB = RGB(192, 192, 192) ' 25% Grey

End With

End Sub

The notations at the end of the first three SeriesCollection refer to cells that have the RGB colour information in them, and all corresponding 31 cell entries are the product of a formula.

Before I can start to look at the variable aspect, is there a way that I can write the macro below so it actually picks up the colour indicated from the cell and show it in the graph. All I am getting at the moment is different run time or syntax messages depending on what I have after the ‘=’ sign.

Thanks

 

[MrKowz]

This is untested, but try:

Sub InterestG1()
Dim i           As Long, _
    strColor    As String


Sheets("InterestG").Activate

With ActiveChart

    For i = 1 To 31
        strColor = Color(Cells(25, i + 1), 2)
        .SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(strColor)
    Next i

End With

End Sub

Function Color(rng As Range, Optional formatType As Integer = 0) As Variant
    Dim colorVal As Variant
    colorVal = Cells(rng.Row, rng.Column).Interior.Color
    Select Case formatType
        Case 1
            Color = Hex(colorVal)
        Case 2
            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        Case 3
            Color = Cells(rng.Row, rng.Column).Interior.colorindex
        Case Else
            Color = colorVal
    End Select
End Function

 

[Red over White]

I have made a couple of cosmetic changes to the code, but even without those I am getting:

Sub InterestG3()

Dim i As Long
strColor As String Compile error - Statement invalid outside Type block
Dim A23 As Integer

A23 = Worksheets("Graph2").Range("A23").Value

Sheets("InterestG").Activate

With ActiveChart

For i = 1 To A23
strColor = Color(Cells(25, i + 1), 2)
.SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(strColor)
Next i

End With

End Sub Should this be at the end of the macro? When I pasted it in it put a line after this entry.

Function Color(rng As Range, Optional formatType As Integer = 0) As Variant
Dim colorVal As Variant
colorVal = Cells(rng.Row, rng.Column).Interior.Color
Select Case formatType
Case 1
Color = Hex(colorVal)
Case 2
Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
Case 3
Color = Cells(rng.Row, rng.Column).Interior.ColorIndex
Case Else
Color = colorVal
End Select
End Function

 

[MrKowz]

Your compile error is a result of adjusting the DIM statement of the code I sent. For your adjustment, you need to ensure that the variable is DIMmed.

Dim i As Long
[B][COLOR="#FF0000"]Dim[/COLOR][/B] strColor As String
Dim A23 As Integer

As for the End Sub - I sent you two pieces of code. One is the sub that is performing the adjustments on your graph. One is a function that is used within that sub to return a string value of the RGB color of the "current" cell.

 

[Red over White]

Apologies, a silly error on my part. The code now reads, although I am now getting a new message

Sub InterestG3()

Dim i As Long
Dim strColor As String
Dim A23 As Integer

A23 = Worksheets("Graph2").Range("A23").Value

Sheets("InterestG").Activate

With ActiveChart

' The first colour selected is in Graph2 "B25"
' The second colour selected is in Graph2 "C25"
' and so on

For i = 1 To A23
strColor = Color(Worksheets("Graph2").Cells(25, i + 1), 2)
.SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(strColor) ' Compile error: Argument not optional
Next i

End With

End Sub

I'm going to have to go away and research Functions, as I haven't used them before. Well I first got your response, I lifted the code into my file and it put a line in after the 'End Sub'. Although I might out in my research, I can't square your statement 'one is a function that is used within that sub' if there is a line between them. I always believed that anything after the 'End Sub' (and the line) meant that was it.

 

[MrKowz]

I incorrectly assumed the RGB function could accept a string. Try the following adjusted code - I integrated the functionality of the Color() function inside the sub, so we can extract the calculated RGB values separately.

Sub InterestG3()

Dim i As Long
Dim A23 As Integer

Dim cCell   As Variant, _
    cRed    As Long, _
    cGreen  As Long, _
    cBlue   As Long
    
A23 = Worksheets("Graph2").Range("A23").Value

Sheets("InterestG").Activate

With ActiveChart

' The first colour selected is in Graph2 "B25"
' The second colour selected is in Graph2 "C25"
' and so on

    For i = 1 To A23
        'Calculate RGB Values
        cCell = Worksheets("Graph2").Cells(25, i + 1).Interior.Color
        cRed = cCell Mod 256
        cGreen = (cCell / 256) Mod 256
        cBlue = (cCell / 65536)
        
        'Adjust line color using RGB values
        .SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(cRed, cGreen, cBlue)
    Next i

End With

End Sub

 

[Red over White]

We are getting there, although the colours in the graph are magenta, the second colour in the sequence

 

[MrKowz]

Are all other colors seemingly correct? It's using the same method to assign all the rest of the colors, so I'd assume the issue lies in the cell referenced to grab the second color. How far off is the color?

 

[Red over White]

All 31 graph lines are Magenta

The formula in Graph2 B25 is =IF(B23="","",VLOOKUP(B23,RTY!$I$2:$K$51,2,FALSE)) and the result is RGB(192, 192, 192) ‘ 25% Grey
The formula in Graph2 C25 is =IF(C23="","",VLOOKUP(C23,RTY!$I$2:$K$51,2,FALSE)) and the result is RGB(255, 0, 255) ‘ Magenta
The formula in Graph2 D25 is =IF(D23="","",VLOOKUP(D23,RTY!$I$2:$K$51,2,FALSE)) and the result is RGB(210, 180, 140) ‘ Tan

If I change the number in ‘For i = 1 To A23’ to either 0 or 2, the SeriesCollection(1) line colour is different but all the other lines remain at magenta. The colour is close to, but not, Ocean and not 25% Grey.

If I disable ‘.SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(cRed, cGreen, cBlue)’, then the graph lines are multicloured, just not the colours I am after.