johaneriksso
10-14-2016, 02:36 AM
Hello!
I have a large Excel file where the data is retrieved from a database and exported to Excel where graphing is formed. The color of the different parts of the graph is controlled by a function in which you fill in a cell in another worksheet, and code reading of it. The problem is that the code will only work as long as there is one color, now I want to use my pattern, in this way, the Format Cells -> Fill -> Pattern style. Therefore, I must add a line of code that says it will color the graph similar to the cell even if it has chosen a pattern style. I copy the code to that part of the function that is relevant. If more of the code to the whole operation is needed or any questions arise I answer directly.
For i = 1 To serCount
If i = serCount + 1 Then Exit For
If .SeriesCollection(i).Name = "" Or inCollection(.SeriesCollection(i).Name, excludedCol) Then
.SeriesCollection(i).Delete
i = i - 1
serCount = serCount - 1
Else
If inCollection(.SeriesCollection(i).Name, areaCol) Then
.FullSeriesCollection(i).ChartType = xlAreaStacked
.FullSeriesCollection(i).Format.Fill.ForeColor.rgb = _
getRGB(findInSub(setupAC, findInCategory(tables, _
Right(tbl.Name, Len(tbl.Name) - 3)).offset(0, 5), areaC, _
.SeriesCollection(i).Name))
Else
.FullSeriesCollection(i).ChartType = xlLineStacked
.FullSeriesCollection(i).Format.line.Weight = 2.5
.FullSeriesCollection(i).Format.line.ForeColor.rgb = _
getRGB(findInSub(setupLC, findInCategory(tables, _
Right(tbl.Name, Len(tbl.Name) - 3)).offset(0, 6), lineC, _
.SeriesCollection(i).Name))
End If
End If
/ Johan
I have a large Excel file where the data is retrieved from a database and exported to Excel where graphing is formed. The color of the different parts of the graph is controlled by a function in which you fill in a cell in another worksheet, and code reading of it. The problem is that the code will only work as long as there is one color, now I want to use my pattern, in this way, the Format Cells -> Fill -> Pattern style. Therefore, I must add a line of code that says it will color the graph similar to the cell even if it has chosen a pattern style. I copy the code to that part of the function that is relevant. If more of the code to the whole operation is needed or any questions arise I answer directly.
For i = 1 To serCount
If i = serCount + 1 Then Exit For
If .SeriesCollection(i).Name = "" Or inCollection(.SeriesCollection(i).Name, excludedCol) Then
.SeriesCollection(i).Delete
i = i - 1
serCount = serCount - 1
Else
If inCollection(.SeriesCollection(i).Name, areaCol) Then
.FullSeriesCollection(i).ChartType = xlAreaStacked
.FullSeriesCollection(i).Format.Fill.ForeColor.rgb = _
getRGB(findInSub(setupAC, findInCategory(tables, _
Right(tbl.Name, Len(tbl.Name) - 3)).offset(0, 5), areaC, _
.SeriesCollection(i).Name))
Else
.FullSeriesCollection(i).ChartType = xlLineStacked
.FullSeriesCollection(i).Format.line.Weight = 2.5
.FullSeriesCollection(i).Format.line.ForeColor.rgb = _
getRGB(findInSub(setupLC, findInCategory(tables, _
Right(tbl.Name, Len(tbl.Name) - 3)).offset(0, 6), lineC, _
.SeriesCollection(i).Name))
End If
End If
/ Johan