<div>
Hi flemingg62,<br />
why do you need VBA? Why not just format one table and save it as a template? Then your formatting and colors will be listed in the chart types as your personal template<br />
kfalandays
</div>


<div>
Hi<br />
Thanks I tried templates and I can't get it to keep the same colour for an item. i.e I want &nbsp;&quot;Vacant&quot; to be blue but Vacant can occur in a different position in the list. Sometime it is 4th, sometime it is 8th. The chart template appears to always assign the same colour to the row. i.e. the 3rd item will always the same colour no matter what the description is .
</div>


<div>
Oh I see, that makes sense then to use VBA<br />
kfalandays
</div>


<div>
Hi,<br />

<blockquote>
I have created a tap called Lookup Codes which contains all the description id all graphs headings and their associated colour.<br />

</blockquote>
Could you send an example of that<br />
<br />
Regards
</div>


<div>
pls try
<pre><code class="code-10 nolinks" id="code-20-41719453-1"><span>Sub ColorChartColumnsbyCellColor()</span>
<span></span>
<span>For Each sh In Worksheets</span>
<span>    For Each chObj In sh.ChartObjects</span>
<span>        With chObj.Chart.SeriesCollection(1)</span>
<span>            Set vAddress = Range(Split(Split(.Formula, &quot;,&quot;)(1), &quot;!&quot;)(1))</span>
<span>            For Idx = 1 To vAddress.Count</span>
<span>                res = 0</span>
<span>                On Error Resume Next ' find corresponding XValue</span>
<span>                res = WorksheetFunction.Match(vAddress.Cells(Idx), Sheets(&quot;Sheet3&quot;).Range(&quot;A1:A5&quot;), 0)</span>
<span>                On Error GoTo 0</span>
<span>                If res &lt;&gt; 0 Then ' if found</span>
<span>                    .Points(Idx).Format.Fill.ForeColor.RGB = Sheets(&quot;Sheet3&quot;).Range(&quot;A1&quot;).Offset(res - 1, 1).Interior.Color</span>
<span>                End If</span>
<span>            Next</span>
<span>        End With</span>
<span>    Next</span>
<span>Next</span>
<span>End Sub</span>
</code></pre>
Regards<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107434/Color-chart-columns-by-cell-color1-.xlsm" target="_blank" class="file-inline" title="EE (28 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Color-chart-columns-by-cell-color1-.xlsm</a>
</div>


Color-chart-columns-by-cell-color1-.xlsm

<div>
Thanks Traveling for the next 2 days, will try on Friday
</div>


<div>
Hi<br />
Thanks for the code. I tried it and it works perfectly, bar one problem. Most of my graphs are Power Pivot and I keep getting out of ranges error. I have tried a few variations but no luck<br />
<br />
I have attached a file that contains one power pivot tab (and 3 tabs of standard charts), any idea?<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1108121/Test-colours-Charts-with-Power-Pivo.xlsm" target="_blank" class="file-inline" title="Excel file with a power pivot tab (606 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Test-colours-Charts-with-Power-Pivo.xlsm</a>
</div>


Test-colours-Charts-with-Power-Pivo.xlsm

<div>
Not at my usual place(unable to open the file)<br />
<br />
I changed the first code to read directly the values of the chart<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-41726482-1"><span>Sub ColorChartColumnsbyCellColor()</span>
<span>Dim aXVal()</span>
<span>For Each sh In Worksheets</span>
<span>    For Each chObj In sh.ChartObjects</span>
<span>        With chObj.Chart.SeriesCollection(1)</span>
<span>            aXVal = .XValues</span>
<span>            For Idx = 1 To UBound(aXVal)</span>
<span>                res = 0</span>
<span>                On Error Resume Next ' find corresponding XValue</span>
<span>                res = WorksheetFunction.Match(aXVal(Idx), Sheets(&quot;Sheet3&quot;).Range(&quot;A1:A5&quot;), 0)</span>
<span>                On Error GoTo 0</span>
<span>                If res &lt;&gt; 0 Then ' if found</span>
<span>                    .Points(Idx).Format.Fill.ForeColor.RGB = Sheets(&quot;Sheet3&quot;).Range(&quot;A1&quot;).Offset(res - 1, 1).Interior.Color</span>
<span>                End If</span>
<span>            Next</span>
<span>        End With</span>
<span>    Next</span>
<span>Next</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
then try ( your labels have to be unique not like in example: 2 &quot;Meeting&quot;)<br />

<pre><code class="code-10 nolinks" id="code-20-41727090-1"><span>Sub ColorChartColumnsbyCellColor()</span>
<span>Dim aXVal()</span>
<span>For Each sh In Worksheets</span>
<span>    For Each chObj In sh.ChartObjects</span>
<span>        For Each SerCol In chObj.Chart.SeriesCollection</span>
<span>            With SerCol</span>
<span>                aXVal = .XValues</span>
<span>                For Idx = 1 To UBound(aXVal)</span>
<span>                    res = 0</span>
<span>                    On Error Resume Next ' find corresponding XValue</span>
<span>                    res = WorksheetFunction.Match(aXVal(Idx), Sheets(&quot;Sheet1&quot;).Range(&quot;A2:A25&quot;), 0)</span>
<span>                    On Error GoTo 0</span>
<span>                    If res &lt;&gt; 0 Then ' if found</span>
<span>                        .Points(Idx).Format.Fill.ForeColor.RGB = Sheets(&quot;Sheet1&quot;).Range(&quot;A2&quot;).Offset(res - 1, 2).Interior.Color</span>
<span>                    End If</span>
<span>                Next</span>
<span>            End With</span>
<span>        Next</span>
<span>    Next</span>
<span>Next</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
Hi<br />
Thanks very much that solved the problem with power pivots. But when I run it in my main reports not all graphs are updated. In it I have a tab called Lookup Colours (to be hidden at the end) and 3 tabs update and 3 don't. I have set up a secondary column of grey so I can see the one that work ( I just change the column reference when I want to run a different colour scheme). Any Idea<br />
<br />
Thanks again for you help so far.<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1108273/BI-Depaartmental-Reports-9.xlsm" target="_blank" class="file-inline" title="BI-Depaartmental-Reports-9.xlsm (13.5 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>BI-Depaartmental-Reports-9.xlsm</a>
</div>


BI-Depaartmental-Reports-9.xlsm

<div>
then try<br />

<pre><code class="code-10 nolinks" id="code-20-41727952-1"><span>Sub ColorChartColumnsbyCellColor()</span>
<span>Dim aXVal()</span>
<span>For Each sh In Worksheets</span>
<span>Debug.Print sh.Name</span>
<span>    For Each chObj In sh.ChartObjects</span>
<span>        With chObj.Chart.SeriesCollection(1)</span>
<span>            aXVal = .XValues</span>
<span>            For Idx = 1 To UBound(aXVal)</span>
<span>                res = 0</span>
<span>                On Error Resume Next ' find corresponding XValue</span>
<span>                res = WorksheetFunction.Match(aXVal(Idx), Range(Sheets(&quot;LookUp Colours&quot;).Range(&quot;A1&quot;), Sheets(&quot;LookUp Colours&quot;).Range(&quot;A&quot; &amp; Rows.Count).End(xlUp)), 0)</span>
<span>                On Error GoTo 0</span>
<span>                If res &lt;&gt; 0 Then ' if found</span>
<span>                    .Points(Idx).Format.Fill.ForeColor.RGB = Sheets(&quot;LookUp Colours&quot;).Range(&quot;A1&quot;).Offset(res - 1, 1).Interior.Color</span>
<span>                End If</span>
<span>            Next</span>
<span>        End With</span>
<span>    Next</span>
<span>Next</span>
<span></span>
<span>End Sub</span>
</code></pre>
You change the name of the sheet (case sensitive)
</div>


<div>
Hi<br />
Thanks and sorry to be a pain, but there is no change, i.e. graph in W1, W3 &amp;&nbsp;M2 won't colour as per the tab &quot;LookUp Colours&quot;. <br />
And I assume the comment about case sensitive refers to &quot;LookUp Colours&quot;.
</div>


<div>
runned the macro and this is the result where is the problem exactly<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w31/1108395/BI-Depaartmental-Reports-9v1.xlsm" target="_blank" class="file-inline" title="EE (13.6 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>BI-Depaartmental-Reports-9v1.xlsm</a>
</div>


BI-Depaartmental-Reports-9v1.xlsm

<div>
When I run the code only half the charts are updated. If I switch the colour to use the grey colour in Column C of Lookup Colour<br />
&nbsp;( &nbsp; &nbsp; &nbsp; .Points(Idx).Format.Fill.F<wbr />oreColor.R<wbr />GB = Sheets(&quot;LookUp Colours&quot;).Range(&quot;A1&quot;).Offs<wbr />et(res - 1, 2).Interior.Color)<br />
So all charts that change colour are Grey. You will see that not all charts are updated. i.e. Tab W3 won't update but tab W2 will
</div>


<div>
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.<br />
<br />
I have recommended this question be closed as follows:<br />
<br />
Accept: Rgonzo1971 (<a href="https:#a41730640" rel="ugc">https:#a41730640</a>)<br />
<br />
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.<br />
<br />
frankhelk<br />
Experts-Exchange Cleanup Volunteer
</div>


<div>
Private Function ColorMyTab()<br />
Dim iCntr, sht<br />
<br />
'This will hold the colorIndex number<br />
iCntr = 2<br />
<br />
'looping throgh the all the sheets of the workbook<br />
For Each sht In ThisWorkbook.Worksheets<br />
&nbsp; &nbsp; iCntr = iCntr + 1<br />
&nbsp; &nbsp; <br />
&nbsp; &nbsp; 'Applying the colors to Sheet tabs<br />
&nbsp; &nbsp; sht.Tab.ColorIndex = iCntr<br />
Next<br />
End Function
</div>


<div>
<div class="content wysiwyg-content">
I have an excel file about 20 tabs of data and graphs and I want to have consistence colouring through out all tables<br />
I have created a tap called Lookup Codes which contains all the description id all graphs headings and their associated colour.<br />
<br />
I found some code on the web the almost works but I can't get it to work with all graphs in all tabs, any ideas<br />
<br />
The attached file is a striped down file I have been using to get a basic solution and my VBA skills are basic<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w30/1107212/Color-chart-columns-by-cell-color1.xlsm" target="_blank" class="file-inline" title="Color-chart-columns-by-cell-color1.xlsm (47 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Color-chart-columns-by-cell-color1.xlsm</a>
</div>
</div>


Color-chart-columns-by-cell-color1.xlsm

I have an excel file about 20 tabs of data and graphs and I want to have consistence colouring through out all tables
I have created a tap called Lookup Codes which contains all the description id all graphs headings and their associated colour.

I found some code on the web the almost works but I can't get it to work with all graphs in all tabs, any ideas

The attached file is a striped down file I have been using to get a basic solution and my VBA skills are basic

How to have consistent colouring of excel charts in many tabs

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.

VB Script

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.