Solved

Lookup pivot and highlight

Posted on 2012-03-27
4
251 Views
Last Modified: 2012-03-28
Hi,

Im loking for some code that will look at the data in ColA in one pivot and if there is a matching entry in ColA in a pivot on another tab, highlight the whole row green, if it is not a  match, highlight it red.

I did the first as an example

Thanks
Seamus
test.zip
0
Comment
Question by:Seamus2626
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
I would use this code:

Sub matchPivots()
Dim wkb As Workbook
Dim wksA As Worksheet
Dim wksB As Worksheet
Dim rng As Range
Dim r As Range
Dim rFind As Range
Dim rColor As Range

    Set wkb = ThisWorkbook
    Set wksA = wkb.Sheets("ASPA Pivot")
    Set wksB = wkb.Sheets("Sophis Pivot")
    
    Set rng = wksA.Range("A5", wksA.Range("A" & Rows.Count).End(xlUp).Offset(-1, 0))
    
    For Each r In rng
        Set rColor = wksA.Range(r, wksA.Cells(r.Row, "G"))
        Set rFind = wksB.Range("A:A").Find(what:=r.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
        If Not rFind Is Nothing Then
            rColor.Interior.Color = vbGreen
        Else
            rColor.Interior.Color = vbRed
        End If
    Next r
    
End Sub

Open in new window


See attached.

Dave
match-pivots-r1.xls
0
 

Author Closing Comment

by:Seamus2626
Comment Utility
Thanks Dave!

Seamus
0
 

Author Comment

by:Seamus2626
Comment Utility
One question Dave, the colours expand to col G, however the pivots shrink and expand with new data. There is always a grand total for for Columns, so could you amend so the colouring only goes as far as "Grand Total"

Thanks
Seamus
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Sure.  It searches for "Grand Total" on row 4, if not found, then uses last column on row 4.  Change the row number if you move the pivot table:

Option Explicit

Sub matchPivots()
Dim wkb As Workbook
Dim wksA As Worksheet
Dim wksB As Worksheet
Dim rng As Range
Dim r As Range
Dim rFind As Range
Dim rColor As Range
Dim lastCol As Long

    Set wkb = ThisWorkbook
    Set wksA = wkb.Sheets("ASPA Pivot")
    Set wksB = wkb.Sheets("Sophis Pivot")
    
    Set rng = wksA.Range("A5", wksA.Range("A" & Rows.Count).End(xlUp).Offset(-1, 0))
    
    On Error Resume Next
    lastCol = wksA.Range("4:4").Find(what:="Grand Total", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext).Column
    If lastCol = 0 Then
        lastCol = wksA.Cells(4, Columns.Count).End(xlToLeft).Column
    End If
    On Error Resume Next
    
    For Each r In rng
        Set rColor = wksA.Range(r, wksA.Cells(r.Row, lastCol))
        Set rFind = wksB.Range("A:A").Find(what:=r.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
        If Not rFind Is Nothing Then
            rColor.Interior.Color = vbGreen
        Else
            rColor.Interior.Color = vbRed
        End If
    Next r
    
End Sub

Open in new window


See attached.

Dave
match-pivots-r2.xls
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now