+ Reply to Thread
Results 1 to 4 of 4

pivot table conditional formatting not applying to certain rows

  1. #1
    Registered User
    Join Date
    01-24-2024
    Location
    CT
    MS-Off Ver
    16130.20868
    Posts
    2

    pivot table conditional formatting not applying to certain rows

    I have successfully created a pivot table and applied conditional formatting rules, but I am running into an issue where certain rows don't have the rules applied. Here are the conditional formatting rules I set (most important are 4 and 5):

    1) All rows with "Service" in column A are yellow

    2) All units with date of over 14 days are highlighted red in the # OF DAYS column

    3) All units with "Internal" in column A are blue

    4) All units with "Internal" in column A and with "PDI" in column H are blue (may be reiterative here as I think rule 3 accomplishes this anyway, but I don't think this affects anything)

    5) All units with "Internal" in column A and with "HOOK LIFT", "TARP", "COVER", "ROTATOR", and "INSTALL" found in row H have entire row highlighted in green. This formatting has been split up into one rule per keyword, not sure how I would combine this in conditional formatting.


    There are a couple of other rules but I do not think they affect anything. Please do let me know if you inspect the conditional formatting and find that it does.

    THE ISSUE:
    If column A is sorted by A-Z, row 16 does not apply rules correctly. See attached screenshots.
    The text "INSTALL" is in row H, which should turn the row green, but the row is highlighted neither green nor blue, even with the rule for "INTERNAL" turning the row blue.

    If column A is sorted by Z-A, row 28 does not apply rules correctly. in the same way.

    If column B is sorted Z-A, we run into another issue:
    "INSTALL" should overwrite the "PDI", but row 16 is still highlighted in blue.

    Can you please take a look at my document and let me know how to fix this?
    Last edited by seci_matt; 01-26-2024 at 02:23 PM.

  2. #2
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: pivot table conditional formatting not applying to certain rows

    I changed your conditional formulas:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    M5 has a list of words to look for.
    Attached Files Attached Files

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: pivot table conditional formatting not applying to certain rows

    All your CF formulas for INTERNAL refer to $A2 in the formula when it should be $A1 since they are applied to row 1 initially (and the col H reference is to $H1)
    Rory

  4. #4
    Registered User
    Join Date
    01-24-2024
    Location
    CT
    MS-Off Ver
    16130.20868
    Posts
    2

    Re: pivot table conditional formatting not applying to certain rows

    Rorya,

    I believe that was the issue -- seems to have fixed it. Not sure how it keeps ending up on $A2 or $H2 (I set it for $A:$A originally, I believe) but it works with $A1! Thank you so much.

+ 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. Conditional Formatting on Pivot Rows - Not Applying to all rows
    By Beefhamburger in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-11-2022, 12:29 PM
  2. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  3. Conditional Formatting rows in Pivot Table HELP!
    By kfryar in forum Excel General
    Replies: 1
    Last Post: 03-26-2014, 01:26 AM
  4. Replies: 8
    Last Post: 04-25-2011, 09:37 AM
  5. Applying Conditional Formatting to Multiple Rows
    By dcaldwell86 in forum Excel General
    Replies: 1
    Last Post: 10-02-2009, 09:32 AM
  6. Applying Formatting to Pivot Table
    By vijay_sankar in forum Excel General
    Replies: 1
    Last Post: 04-18-2007, 08:57 AM
  7. Applying conditional formatting to ODD rows
    By SirSFZ in forum Excel General
    Replies: 0
    Last Post: 03-31-2005, 10:46 AM

Tags for this Thread

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