• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set colors of lines in line graph to match cell color when cell color is Conditionally Formatted

K

KWD

New Member
Hi I am trying to complete a task at work but unfortunately for me I know nothing about VBA/Macro coding. I found the code below online and it works great but not when I conditionally format the cells then excel reads a white background and my lines are white. I believe there could be a simple tweak using a previous forum I found here but I have no clue where to insert this tweak. Here is what I found:

"However , the following line in the code retrieves the color of the worksheet cell :

SourceRangeColor = SourceRange.Interior.Color

When a cell is conditionally formatted , the following statement can retrieve the color of the cell :

Activecell.FormatConditions(1).Interior.color

This assumes that the cell has only one CF formula.

If you can upload a complete workbook with the data and the chart , it will make everyone's job easier."
I need to use conditional formatting because on a weekly basis a brand may change from 2nd to 3rd and I need the color to go with it. here is the code that works FINE when I fill the cells. Can you help tweak it to work with a conditional format? Thank you all!!



Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long
'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects
'Loop through all series in the target chart
For Each MySeries In oChart.Chart.SeriesCollection

'Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, ",")

'Capture the first cell in the source range then trap the color
Set SourceRange = Range(FormulaSplit(2)).Item(1)
SourceRangeColor = SourceRange.Interior.Color
On Error Resume Next
'Coloring for Excel 2003
MySeries.Interior.Color = SourceRangeColor
MySeries.Border.Color = SourceRangeColor
MySeries.MarkerBackgroundColorIndex = SourceRangeColor
MySeries.MarkerForegroundColorIndex = SourceRangeColor

'Coloring for Excel 2007 and 2010
MySeries.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor

Next MySeries
Next oChart
End Sub
 
Hi ,

Unfortunately , it is not such a simple tweak ; when a cell is conditionally formatted , since the color cannot be read directly , it is the format conditions which need to be evaluated ; if you have multiple colors , there will be multiple conditions ; each of them has to be evaluated to detect which of them is responsible for coloring the cell its present color. The code is complex.

Narayan
 
Hi Narayan,

Crazy thing is I was reading a thread you were on which is where I got my assumption.

Since you say its complex would you suggest that someone with no VBA experience attempt such a code? I'm looking at my screen like its a foreign language right now and no one here has the knowledge to help me either.
 
Hi ,

If you can upload your file , with sample data , it will be easier to come up with a solution or a workaround.

Narayan
 
Hi Narayan,

I uploaded a sample document of something similar to what I am working with. A quick overview of the document:

I already did the conditional formatting and input the VBA code I found online. I created the line graphs that I need to be the same color as the filled cells. I would also like the data labels to be the same color as the filled cells but that's just a prayer.

Now why I am using conditional formatting is because the brands a referenced cells that can change every week depending on how well the brand does. So for in this instance if Company A Trades places with Company C I need the colors to change with them.

I hope this helps some, fingers crossed.
 

Attachments

Hi ,

I am getting an error when I try to open the file ; the error message is that the file type does not match the extension.

Can you download the file using your link from your post and confirm that you are able to open the file ?

Narayan
 
Using the company computer the file must be encrypted.

If I typed it up into a Google Doc would that work? I don't think I would be able to do any conditional formatting that way though.
 
Hi ,

Is it possible you can email me the file after you get home , so that it is not encrypted ? Otherwise , is there any way in which it can be decrypted ?

Narayan
 
Hi,

Once I get home I will create a file and upload it, unfortunately I cannot decrypt the file without approval and even though the boss wants this done I cant see this forum being approved.
 
Hi Narayan,

So yesterday as I continued to work on it I found a different code which allowed me to take a different method. I am now using something like a color index that the code references when it executes and it removes the need for conditional formatting. Below is the code that I used and it is working! Thank you again for offering your help.


Sub ColorAndMarkerBySeriesName()
Dim rPatterns As Range
Dim iSeries As Long
Dim rSeries As Range
Dim iColorIndex As Long
Dim oChart As ChartObject
Set rPatterns = ActiveSheet.Range("A14:A31")
For Each oChart In ActiveSheet.ChartObjects
With oChart.Chart
For iSeries = 1 To .SeriesCollection.Count
Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name)
If Not rSeries Is Nothing Then
iColorIndex = rSeries.Interior.ColorIndex
With .SeriesCollection(iSeries)
.Border.ColorIndex = iColorIndex
.MarkerForegroundColorIndex = iColorIndex
.MarkerBackgroundColorIndex = iColorIndex
On Error Resume Next
.DataLabels.Font.ColorIndex = rSeries.Font.ColorIndex
.DataLabels.Font.Bold = True
On Error GoTo 0
End With
Select Case LCase$(rSeries.Offset(, 1).Value)
Case "square"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleSquare
Case "circle"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleCircle
Case "triangle"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleTriangle
Case "diamond"
.SeriesCollection(iSeries).MarkerStyle = xlMarkerStyleDiamond
End Select
End If
Next
End With
Next
End Sub
 
You must log in or register to reply here.
Back
Top