Solved

Conditionally format a row in access pivot table to change color based on the rows values

Posted on 2012-09-13
1,905 Views
I have a pivot table in Access 2007 with multiple rows and columns.
I need each row to change color based on the values in the table.

As the cells go from left to right, if they are getting lower, I need to change the row red.
If they are getting higher, I need to change the row to green.
If the numbers remain the same, no change is needed.

If possible, I would like the color change to begin at the point where the numbers start changing and continue to the right.  If that is not feasible, it is fine to just change the color of the entire row.
0
Question by:TARDEC

LVL 29

Expert Comment

I can't quite visualize what you're saying.  A graphic would be helpful here.
0

Author Comment

Here is my horrible mspaint mockup.
if the numbers go up, green
if the numbers go down, red

i didn't include this in my example, but if I could have a row be one color as the numbers trended in one direction and then switch colors when they trended in the opposite, that would be perfect.
pivottable-mockup.png
0

LVL 29

Expert Comment

I'm still baffled. Let's take the second green row.
1  2  2  1  1  1  1
Can you explain why that would be green?
0

Author Comment

it goes from 1 to 2, the numbers went up, so it goes green.
if I could get it to go to red once the numbers went back down, that would be ideal, but it's the initial change that I'm concerned with for the moment.
0

LVL 29

Expert Comment

one color as the numbers trended in one direction and then switch colors when they trended in the opposite
I don't understand what you mean here either.
0

Author Comment

That is what I clarified in my last post.

1 -1 -2 -2 -1 -1 -1 -1
The first change in the numbers is that they go up (from 1 -2), so the row is green.

If I could get the last four 1's to be red because it went back down from 2-1, that would be ideal.
0

LVL 29

Expert Comment

Oh so you mean the color can fluctuate between green and red within a single row?
0

Author Comment

Yes, fluctuating between the colors is preferable.

If that is not possible, I would just like to color the row a single color based upon the first change in numbers going from left to right.
0

Author Comment

Here is a mockup of how it would look if the colors flucuated.
pivottable-mockup2.png
0

LVL 29

Expert Comment

The one based on the first change is easier of course.  With the one that fluctuates, how would you handle something like 1  2  1  2  1  2  1  2?
You would color fields 1 and 2 green because it is going up.  But you would also color fields 2 and 3 red because it is going down, and fields 3 and 4 green, etc...
0

Author Comment

The twos would be green because that is the cell where it goes up.
The ones would be red because that is the cell where it goes down

so the first 1 would be white because there was no change yet, then it would alternate
green - red -green - red - green - red...etc
0

LVL 29

Expert Comment

So you only want to color the following cell.  I see what you're getting at now.
0

LVL 29

Expert Comment

Create a continuous form based on your crosstab.
Add conditional formatting from the 2nd field onwards.
Your conditions should look something like this:
0

Author Comment

Now, I'm not sure what you mean by create a continuous form based on your crosstab
0

LVL 29

Expert Comment

Crosstab and pivot table are essentially the same.  Just select your table in the navigation pane and click on this in the image below.
0

Author Comment

When I do that, it just gives me a listing of my entries in the original database table, not the filtered & calculated pivot table that I created
0

LVL 29

Expert Comment

Oh, so you're using the new Pivot Table View.  I don't believe you can do it with this view.  You need to create a Crosstab query using the Query wizard.  This will give you the same pivot view.  Then use that crosstab query for your continuous form.
0

Author Comment

Ok, for some reason the crosstab query wizard is disabled on my system and I am not allowed to enable it.

Is this possible at all using VBA?  Or, am I just out of options here.
0

LVL 29

Expert Comment

How do you see it as disabled?  Is there a message that comes up when you select crosstab query wizard?
0

Author Comment

Yes, a popup comes up telling me that it has been disabled and to reenable it from the access options menu, but I don't have those privleges on my computer.
0

LVL 29

Expert Comment

Can you create the query in another computer that has the wizard enabled?
0

Author Comment

Unless I'm understanding the other capabilities incorrectly, it looks like a VBA solution is the only way I can do this since I need the dynamic ability to run the form and then have the multiple filters able to be updated ad hoc.

Right now I'm unfortunately going to have to export this to Excel where this type of formatting is simple, unless I can quickly figure out how in Access.
0

LVL 29

Accepted Solution

Sure, doing it in Excel is another option.  However, if you still want to try to do it in Access, here's a link showing you how you can create the Crosstab query manually since you don't have the wizard.

http://www.dotnetheaven.com/article/create-a-crosstab-query-in-access-2010
0

LVL 29

Expert Comment

http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26
0

Author Comment

I appreciate your help, and though it might eventually work doing it the way you suggested, I really need to do this particular project in a pivot table and not using the crosstab query.
0

LVL 29

Expert Comment

Well, conditional formatting is not available on the Pivot Table view as I mentioned; however, it may be possible to do it in VBA using this code I just found from a Microsoft page:
Me.PivotTable.Activeview.DataAxis.FieldSets(0).Fields(0).DetailBackColor = "Green"

If you still want to, I could give it a shot using the above but you would need to load a copy of your database with just the pivot table view and some records (with privacy information removed).  You could do that here or in a new question if you want.
0

Featured Post

In Debugging â€“ Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can usâ€¦
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I donâ€™t intend to describe all of the uses of temporary tables in tâ€¦
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). Uâ€¦
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all recordâ€¦