Solved

Compare Pivot Table Columns with Conditional Formatting.

Posted on 2011-03-10

Microsoft Excel

1 solution

329 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:

What 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.

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

Join The Community

5 Comments

LVL 6

Accepted Solution

by:royhsiao

royhsiao earned 500 total points

ID: 350982422011-03-10

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

Author Comment

by:thomas-sherrouse

ID: 350985642011-03-10

Is there any way to do it without a Macro?

LVL 6

Expert Comment

by:royhsiao

ID: 351017802011-03-10

you could use conditional formatting but it will take a while.

Author Comment

by:thomas-sherrouse

ID: 351083242011-03-11

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.

LVL 6

Expert Comment

by:royhsiao

ID: 351089382011-03-11

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

Featured Post

Question has a verified solution.

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

Excel code to find and report on links in the selected ActiveWorkbook (formulas, range names, pivotTables & charts)

Article by: Dave

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…