• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2228
  • Last Modified:

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

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
TARDEC
Asked:
TARDEC
  • 14
  • 12
1 Solution
 
IrogSintaCommented:
I can't quite visualize what you're saying.  A graphic would be helpful here.
0
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
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
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
Oh so you mean the color can fluctuate between green and red within a single row?
0
 
TARDECAuthor Commented:
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
 
TARDECAuthor Commented:
Here is a mockup of how it would look if the colors flucuated.
pivottable-mockup2.png
0
 
IrogSintaCommented:
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
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
So you only want to color the following cell.  I see what you're getting at now.
0
 
IrogSintaCommented:
Create a continuous form based on your crosstab.
Add conditional formatting from the 2nd field onwards.
Your conditions should look something like this:
cond format
0
 
TARDECAuthor Commented:
Now, I'm not sure what you mean by create a continuous form based on your crosstab
0
 
IrogSintaCommented:
Crosstab and pivot table are essentially the same.  Just select your table in the navigation pane and click on this in the image below.pix
0
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
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
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
How do you see it as disabled?  Is there a message that comes up when you select crosstab query wizard?
0
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
Can you create the query in another computer that has the wizard enabled?
0
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
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
 
IrogSintaCommented:
It's customary to explain a grade of "C" if you read this link:
http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=26
0
 
TARDECAuthor Commented:
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
 
IrogSintaCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 14
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now