Solved

Compare Pivot Table Columns with Conditional Formatting.

Posted on 2011-03-10
5
328 Views
Last Modified: 2012-06-27
I've got a Pivot Table set up the reviews a set of Projects. I want to highlight a value that is larger than the value in the next column. I know I could just do a formula or an IF statement and hide that, but I want to have something clean and easy.

Here's a screenshot of part of my Pivot Table:
 PrjPivotExampleWhat I want to do is Highlight the value in the "Total at Billing" column if it is larger than the value in the "Rev Budget" column.

In this example Row 12 should be highlighted.

0
Comment
Question by:thomas-sherrouse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
royhsiao earned 500 total points
ID: 35098242
try this
Sub test()
Dim i As Integer
Dim j As Integer

i = Range("T65536").End(xlUp).Row
For j = 1 To i
If Cells(j, 20) > Cells(j, 21) And IsNumeric(Cells(j, 20)) = True Then
    Cells(j, 20).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Else
End If
Next j
End Sub

Open in new window

0
 

Author Comment

by:thomas-sherrouse
ID: 35098564
Is there any way to do it without a Macro?
0
 
LVL 6

Expert Comment

by:royhsiao
ID: 35101780
you could use conditional formatting but it will take a while.
0
 

Author Comment

by:thomas-sherrouse
ID: 35108324
I've already got Conitional Formatting set up to highlight negative numbers, but instead of Highlight if less than $0 I want to highlight if less than the cell value to the left of the current number. This is a report that will be updated so I don't want any static or frozen references.
0
 
LVL 6

Expert Comment

by:royhsiao
ID: 35108938
You could try to call the macro when the selection has change or pivot table update
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call test
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call test
End Sub

Open in new window

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question