+ Reply to Thread
Results 1 to 8 of 8

Pivot Table Conditional Formatting

Hybrid View

  1. #1
    Registered User
    Join Date
    11-08-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Pivot Table Conditional Formatting

    Hey everyone, I have a very specific problem that I'm struggling to solve. I think I'm fairly proficient with Excel, but I'm struggling with this one.

    We are setting up a document to track projects that have been signed off on or are still estimates with the client. Essentially I have a data entry tab where you enter a bunch of information about the project, including a "Estimate?" column where you choose whether the project is an "Estimate" or is "Approved".

    This data outputs to a pivot table (actually 4 pivot tables, based on the type of project) and lists all projects by client name. The main values in the pivot table are essentially total cost of the project by month and client (i.e. months are in a row across the top, client name in the column to the left).

    I'd like to make it so that any cells that correspond to a project that is not approved (i.e. the last cell for that row is "Estimate") would be highlighted yellow so we know that project is not approved yet. Is there any way to do some conditional formatting tied to that estimate field (which I don't want showing up in the pivot table).


    Thanks!

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Pivot Table Conditional Formatting

    It should be possible but you need to post example workbook so we can see the data and table. You can use fake data it's the layout that important.

    Do you have multiple projects for a client?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    11-08-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot Table Conditional Formatting

    Yes it does, as you'll see in the attached workbook!

    Thanks for any insight you can provide.Forecast Example.xlsm

    EDIT: I totally forgot - password for the workbook is 123

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Pivot Table Conditional Formatting

    password '123' (without quotes) does not work

  5. #5
    Registered User
    Join Date
    11-08-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot Table Conditional Formatting

    Ugh. Sorry ... I must have uploaded the wrong version of the file.

    I saved a non-password protected version of the file this time, sorry for being completely obnoxious haha.
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Pivot Table Conditional Formatting

    right click Team 1 sheet and View code.

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
        HighlightEstimates Target
        
    End Sub
    Code module
    Public Sub HighlightEstimates(PTable As PivotTable)
    
        Dim vntMatch As Variant
        Dim rngDataEntry As Range
        Dim lngRow As Long
        Dim lngRowDE As Long
        Dim strTeam As String
        Dim strClient As String
        Dim strJob As String
        Dim blnHighlight As Boolean
        
        Set rngDataEntry = ThisWorkbook.Worksheets("Data Entry Tab").Range("A1").CurrentRegion
        strTeam = PTable.Parent.Name
        
        For lngRow = 1 To PTable.RowRange.Rows.Count
            If PTable.RowRange.Cells(lngRow, 1).IndentLevel = 1 Then
                ' job
                strJob = PTable.RowRange.Cells(lngRow, 1)
                blnHighlight = False
                For lngRowDE = 2 To rngDataEntry.Rows.Count
                    If rngDataEntry.Cells(lngRowDE, 1) = strTeam Then
                        If rngDataEntry.Cells(lngRowDE, 2) = strClient Then
                            If rngDataEntry.Cells(lngRowDE, 5) = strJob Then
                                If rngDataEntry.Cells(lngRowDE, 12) = "Estimate" Then
                                    blnHighlight = True
                                    Exit For
                                End If
                            End If
                        End If
                    End If
                Next
                
                If blnHighlight Then
                    Intersect(PTable.TableRange1, PTable.RowRange(lngRow, 1).EntireRow).Interior.Color = vbYellow
                End If
            Else
                ' client
                strClient = PTable.RowRange.Cells(lngRow, 1)
            End If
        Next
    End Sub

  7. #7
    Registered User
    Join Date
    11-08-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot Table Conditional Formatting

    Thank you, that is pretty close to what I'm looking for. I only have one issue...

    If you look at Job 8 - one month's entries (January) are approved, while the next month's entries are estimates.

    Can I simply update the code and change "entire row" to something else that will highlight only the cells that are effected by estimates?

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Pivot Table Conditional Formatting

    revision

    Public Sub HighlightEstimates(PTable As PivotTable)
    
        Dim vntMatch As Variant
        Dim rngDataEntry As Range
        Dim lngRow As Long
        Dim lngRowDE As Long
        Dim strTeam As String
        Dim strClient As String
        Dim lngYear As Long
        Dim strJob As String
        Dim blnHighlight As Boolean
        Dim lngCol As Long
        Dim strMonth As String
        
        Set rngDataEntry = ThisWorkbook.Worksheets("Data Entry Tab").Range("A1").CurrentRegion
        strTeam = PTable.Parent.Name
        
        strTeam = PTable.PageFields("Account Team").LabelRange.Offset(0, 1).Value
        lngYear = CLng(PTable.PageFields("Year").LabelRange.Offset(0, 1).Value)
        
        For lngCol = 2 To PTable.ColumnRange.Columns.Count
            If Len(PTable.ColumnRange.Cells(2, lngCol)) > 0 Then
                strMonth = PTable.ColumnRange.Cells(2, lngCol)
            End If
            For lngRow = 1 To PTable.RowRange.Rows.Count
                If PTable.RowRange.Cells(lngRow, 1).IndentLevel = 1 Then
                    ' job
                    strJob = PTable.RowRange.Cells(lngRow, 1)
                    blnHighlight = False
                    For lngRowDE = 2 To rngDataEntry.Rows.Count
                        If rngDataEntry.Cells(lngRowDE, 1) = strTeam Then
                            If rngDataEntry.Cells(lngRowDE, 2) = strClient Then
                                If rngDataEntry.Cells(lngRowDE, 5) = strJob Then
                                    If rngDataEntry.Cells(lngRowDE, 6) = lngYear Then
                                        If rngDataEntry.Cells(lngRowDE, 7) = strMonth Then
                                            If rngDataEntry.Cells(lngRowDE, 12) = "Estimate" Then
                                                blnHighlight = True
                                                Exit For
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    Next
                    
                    If blnHighlight Then
                        Intersect(PTable.ColumnRange.Cells(2, lngCol).MergeArea.EntireColumn, Intersect(PTable.TableRange1, PTable.RowRange(lngRow, 1).EntireRow)).Interior.Color = vbYellow
                    End If
                Else
                    ' client
                    strClient = PTable.RowRange.Cells(lngRow, 1)
                End If
            Next
        Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot Table - Conditional Formatting
    By lewny1983 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-27-2012, 04:51 AM
  2. [SOLVED] Conditional Formatting in a Pivot Table
    By michaelleckey in forum Excel General
    Replies: 7
    Last Post: 04-07-2012, 11:24 PM
  3. Conditional Formatting a pivot table
    By thestappa in forum Excel General
    Replies: 0
    Last Post: 09-01-2010, 04:04 PM
  4. Pivot Table (conditional formatting)
    By traci_marie in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 10-06-2007, 07:53 PM
  5. [SOLVED] Is it possible to add conditional formatting in a pivot-table?
    By Saddy in forum Excel General
    Replies: 1
    Last Post: 07-04-2006, 01:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1