I have several pivot charts and chart worksheets in my workbook. Additionally, I have several parameters setup where the data series will change based on the criteria selected.
How do I create a macro, where no matter what the criteria is set that the color format for each specific data series in all charts and worksheets.
Ideally, I would have the color selection pulling data from a worksheet table.
Thank you for your help in advance. This is really bothering me.
Example:
<table border="0" cellpadding="0" cellspacing="0" width="652"><col style="mso-width-source:userset;mso-width-alt:8411;width:173pt" width="230"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="width:48pt" span="3" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl576" style="height:15.0pt;width:173pt" height="20" width="230">Description</td> <td class="xl577" style="width:73pt" width="97">Type</td> <td class="xl577" style="width:48pt" width="64">Color</td> <td class="xl577" style="width:52pt" width="69">VBA</td> <td class="xl577" style="width:48pt" width="64">Red</td> <td class="xl577" style="width:48pt" width="64">Green</td> <td class="xl577" style="width:48pt" width="64">Blue</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Los Angeles Angels of Anaheim</td> <td>American</td> <td class="xl578">Red</td> <td class="xl575">3</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Oakland Athletics</td> <td>American</td> <td class="xl578">Dark Blue</td> <td class="xl575">25</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">128</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Texas Rangers</td> <td>American</td> <td class="xl578">Orange</td> <td class="xl575">46</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">102</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Seattle Mariners</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Chicago White Sox</td> <td>American</td> <td class="xl578">Purple</td> <td class="xl575">21</td> <td class="xl579" style="width:48pt" width="64">102</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">102</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Cleveland Indians</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Kansas City Royals</td> <td>American</td> <td class="xl578">Magenta</td> <td class="xl575">7</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Minnesota Twins</td> <td>American</td> <td class="xl578">Green</td> <td class="xl575">4</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Detroit Tigers</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Boston Red Sox</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New York Yankees</td> <td>American</td> <td class="xl578">Yellow</td> <td class="xl575">6</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Toronto Blue Jays</td> <td>American</td> <td class="xl578">Cyan</td> <td class="xl575">8</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Baltimore Orioles</td> <td>American</td> <td class="xl578">Blue</td> <td class="xl575">5</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Tampa Bay Rays</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Chicago Cubs</td> <td>National</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">St. Louis Cardinals</td> <td>National</td> <td class="xl578">Black</td> <td class="xl575">1</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Milwaukee Brewers</td> <td>National</td> <td class="xl578">Grey</td> <td class="xl575">16</td> <td class="xl579" style="width:48pt" width="64">128</td> <td class="xl579" style="width:48pt" width="64">128</td> <td class="xl579" style="width:48pt" width="64">128</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Pittsburgh Pirates</td> <td>National</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Houston Astros</td> <td>National</td> <td class="xl578">Green</td> <td class="xl575">4</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Cincinnati Reds</td> <td>National</td> <td class="xl578">Red</td> <td class="xl575">3</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">San Diego Padres</td> <td>National</td> <td class="xl578">Blue</td> <td class="xl575">5</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Arizona Diamondbacks</td> <td>National</td> <td class="xl578">Orange</td> <td class="xl575">46</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">102</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Los Angeles Dodgers</td> <td>National</td> <td class="xl578">Dark Blue</td> <td class="xl575">25</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">128</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">San Francisco Giants</td> <td>National</td> <td class="xl578">Magenta</td> <td class="xl575">7</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Colorado Rockies</td> <td>National</td> <td class="xl578">Black</td> <td class="xl575">1</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Atlanta Braves</td> <td>National</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Washington Nationals</td> <td>National</td> <td class="xl578">Magenta</td> <td class="xl575">7</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New York Mets</td> <td>National</td> <td class="xl578">Cyan</td> <td class="xl575">8</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Philadelphia Phillies</td> <td>National</td> <td class="xl578">Black</td> <td class="xl575">1</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> </tbody></table>
How do I create a macro, where no matter what the criteria is set that the color format for each specific data series in all charts and worksheets.
Ideally, I would have the color selection pulling data from a worksheet table.
Thank you for your help in advance. This is really bothering me.
Example:
<table border="0" cellpadding="0" cellspacing="0" width="652"><col style="mso-width-source:userset;mso-width-alt:8411;width:173pt" width="230"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="width:48pt" span="3" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl576" style="height:15.0pt;width:173pt" height="20" width="230">Description</td> <td class="xl577" style="width:73pt" width="97">Type</td> <td class="xl577" style="width:48pt" width="64">Color</td> <td class="xl577" style="width:52pt" width="69">VBA</td> <td class="xl577" style="width:48pt" width="64">Red</td> <td class="xl577" style="width:48pt" width="64">Green</td> <td class="xl577" style="width:48pt" width="64">Blue</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Los Angeles Angels of Anaheim</td> <td>American</td> <td class="xl578">Red</td> <td class="xl575">3</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Oakland Athletics</td> <td>American</td> <td class="xl578">Dark Blue</td> <td class="xl575">25</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">128</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Texas Rangers</td> <td>American</td> <td class="xl578">Orange</td> <td class="xl575">46</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">102</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Seattle Mariners</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Chicago White Sox</td> <td>American</td> <td class="xl578">Purple</td> <td class="xl575">21</td> <td class="xl579" style="width:48pt" width="64">102</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">102</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Cleveland Indians</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Kansas City Royals</td> <td>American</td> <td class="xl578">Magenta</td> <td class="xl575">7</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Minnesota Twins</td> <td>American</td> <td class="xl578">Green</td> <td class="xl575">4</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Detroit Tigers</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Boston Red Sox</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New York Yankees</td> <td>American</td> <td class="xl578">Yellow</td> <td class="xl575">6</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Toronto Blue Jays</td> <td>American</td> <td class="xl578">Cyan</td> <td class="xl575">8</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Baltimore Orioles</td> <td>American</td> <td class="xl578">Blue</td> <td class="xl575">5</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Tampa Bay Rays</td> <td>American</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Chicago Cubs</td> <td>National</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">St. Louis Cardinals</td> <td>National</td> <td class="xl578">Black</td> <td class="xl575">1</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Milwaukee Brewers</td> <td>National</td> <td class="xl578">Grey</td> <td class="xl575">16</td> <td class="xl579" style="width:48pt" width="64">128</td> <td class="xl579" style="width:48pt" width="64">128</td> <td class="xl579" style="width:48pt" width="64">128</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Pittsburgh Pirates</td> <td>National</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Houston Astros</td> <td>National</td> <td class="xl578">Green</td> <td class="xl575">4</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Cincinnati Reds</td> <td>National</td> <td class="xl578">Red</td> <td class="xl575">3</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">San Diego Padres</td> <td>National</td> <td class="xl578">Blue</td> <td class="xl575">5</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Arizona Diamondbacks</td> <td>National</td> <td class="xl578">Orange</td> <td class="xl575">46</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">102</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Los Angeles Dodgers</td> <td>National</td> <td class="xl578">Dark Blue</td> <td class="xl575">25</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">128</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">San Francisco Giants</td> <td>National</td> <td class="xl578">Magenta</td> <td class="xl575">7</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Colorado Rockies</td> <td>National</td> <td class="xl578">Black</td> <td class="xl575">1</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Atlanta Braves</td> <td>National</td> <td class="xl578">Pale Yellow</td> <td class="xl575">36</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">153</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Washington Nationals</td> <td>National</td> <td class="xl578">Magenta</td> <td class="xl575">7</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">New York Mets</td> <td>National</td> <td class="xl578">Cyan</td> <td class="xl575">8</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">255</td> <td class="xl579" style="width:48pt" width="64">255</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Philadelphia Phillies</td> <td>National</td> <td class="xl578">Black</td> <td class="xl575">1</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> <td class="xl579" style="width:48pt" width="64">0</td> </tr> </tbody></table>
