Change Cell Background Color Macro

boldcode · Aug 5, 2011

Hello,

The Macro I hope to create will work against a pivot table. I will explain below what I would like the Macro to do.

I have three column fields in my pivot table. The first column field is found in Cell A9 by the name of “Type” and what I would like the Macro to do is not only change the cell background color of the cell that contains the value "M & A” to Yellow, but also change the cell background color to yellow for all the cells right under "M & A" to yellow that are associated to the value "M & A". Then do the same for the value “People” except the color would be red instead of Yellow.

For example:
If the value of "M & A" is in cell A10, then cell A10 and all the cells under cell A10 (e.g. A11, A12, A13, A14) that are grouped with "M & A" would have a background color of yellow.

Here is a data sample:

<table style="width: 425px; height: 273px;" border="0" cellpadding="0" cellspacing="0"><col style="width:53pt" span="4" width="70"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:53pt" height="20" width="70">
</td> <td style="width:53pt" width="70">Column A</td> <td style="width:53pt" width="70">Column B</td> <td style="width:53pt" width="70">Column C</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 9</td> <td class="xl64">Type</td> <td class="xl65" style="border-left:none">Year</td> <td class="xl66" style="border-left:none">Rate</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 10</td> <td class="xl67">M&A</td> <td class="xl63" style="border-top:none">2011</td> <td class="xl68" style="border-top:none;border-left:none">3.5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 11</td> <td class="xl67"> </td> <td class="xl63" style="border-top:none">2012</td> <td class="xl68" style="border-top:none;border-left:none">5.6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 12</td> <td class="xl67"> </td> <td class="xl63" style="border-top:none">2013</td> <td class="xl68" style="border-top:none;border-left:none">2.1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 13</td> <td class="xl67"> </td> <td class="xl63" style="border-top:none">2014</td> <td class="xl68" style="border-top:none;border-left:none">4.5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 14</td> <td class="xl67"> </td> <td class="xl63" style="border-top:none">2015</td> <td class="xl68" style="border-top:none;border-left:none">2.3</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 15</td> <td class="xl69">People</td> <td class="xl63" style="border-top:none;border-left:none">2011</td> <td class="xl68" style="border-top:none;border-left:none">1.2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 16</td> <td class="xl67"> </td> <td class="xl63" style="border-top:none">2012</td> <td class="xl68" style="border-top:none;border-left:none">5.2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 17</td> <td class="xl67"> </td> <td class="xl63" style="border-top:none">2013</td> <td class="xl68" style="border-top:none;border-left:none">6.2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 18</td> <td class="xl67"> </td> <td class="xl63" style="border-top:none">2014</td> <td class="xl68" style="border-top:none;border-left:none">2.9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Row 19</td> <td class="xl70"> </td> <td class="xl72" style="border-top:none">2015</td> <td class="xl71" style="border-top:none;border-left:none">2.4</td> </tr> </tbody></table>

Thanks,

BC

jbeaucaire · Aug 5, 2011

This can be accomplished with Conditional Formatting, don't really need a macro. Highlight A9 through A??? then apply the CF formulas shown.

Excel Workbook

A

B

C

8

Type

Year

Rate

9

M&A

2011

3.5

10

2012

5.6

11

2013

2.1

12

2014

4.5

13

2015

2.3

14

People

2011

1.2

15

2012

5.2

16

2013

6.2

17

2014

2.9

18

2015

2.4

Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A9	1. / Formula is =LOOKUP(2,1/($A$9:$A9>""),$A$9:$A9)="M&A"	Abc
A9	2. / Formula is =LOOKUP(2,1/($A$9:$A9>""),$A$9:$A9)="People"	Abc

boldcode · Aug 5, 2011

jbeaucaire,

The pivot table that I am using is created by vba code. I have a button that recreates the pivot table every time if need be. The conditional formatting works on it own, but when I recreate the pivot table by clicking a command button with vba code behind it, the conditional formatting you suggested does not hold up.

jbeaucaire · Aug 5, 2011

Now that I had a working "method" to do it using CF, I would apply this functionality to my complex macro, too. You can record yourself manually applying the CF to the existing table range, then tweak to to make it dynamic.

boldcode · Aug 5, 2011

jbeaucaire,

I understand what you are saying, but there should be a way to do all what I want to do using vba code.

Thanks,

jbeaucaire · Aug 5, 2011

Yes, I just said that. I've shown you how to do it with CF, so you could use that method to record a macro of you do it manually once, then edit that code to be dynamic and add it to your existing code.

Record your macro to get the base code, then we can help edit it down if you still need help. VBA, yes indeed.

Change Cell Background Color Macro

boldcode

Active Member

Excel Facts

jbeaucaire

Well-known Member

boldcode

Active Member

jbeaucaire

Well-known Member

boldcode

Active Member

jbeaucaire

Well-known Member

Similar threads

Forum statistics

Share this page

Change Cell Background Color Macro

boldcode

Active Member

Excel Facts

jbeaucaire

Well-known Member

boldcode

Active Member

jbeaucaire

Well-known Member

boldcode

Active Member

jbeaucaire

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock