+ Reply to Thread
Results 1 to 6 of 6

Problem with Conditional Formatting when table is sorted

  1. #1
    Registered User
    Join Date
    08-09-2013
    Location
    Columbia, MD
    MS-Off Ver
    Excel 2010
    Posts
    19

    Problem with Conditional Formatting when table is sorted

    I have a spreadsheet that keeps track of my golf scores and statistics. The courses that I have played are included in an Excel table (Course Table). This table is used as the source for a Data Validation list when entering my weekly golf results in another area of the spreadsheet. I want the golf courses entered as part of my weekly results to be color coded therefore I use conditional formatting to compare the course entered in the weekly results area to the courses in the Course Table to change the fill color. The conditional formatting works fine until I sort the Course Table. When I play a new course I add it to the Course Table and sort the table alphabetically to make finding the course in the data validation drop down box easier. Unfortunately when I sort this table it changes the colors of the courses entered in the weekly results. I’ve tried every combination of absolute and relative references in the conditional formatting equations with no success.
    I have attached a simplified version of my spreadsheet for review. The current conditional formatting equation for one of the courses is:

    =I5=$C$5

    where I5 is the golf course in the weekly results and $C$5 is the golf course in the Course Table. Any help would be appreciated!

    Thanks,
    Rick
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,078

    Re: Problem with Conditional Formatting when table is sorted

    Why not use
    =I5="Laytonsville"

  3. #3
    Registered User
    Join Date
    08-09-2013
    Location
    Columbia, MD
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Problem with Conditional Formatting when table is sorted

    Fluff13 - Thanks for the response.

    Your suggestion is what I've implemented into my spreadsheet to get it to work. I would still like to understand if there is a way for a conditional format equation (or any equation for that matter) that references a value in a cell in a table can adjust when the table is sorted such that it references the same value in its new, sorted location.

    Thanks again,
    Rick

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Problem with Conditional Formatting when table is sorted

    I have sorted your table a bunch of different ways (date hi/low, date low/hi, course a/z, z/a) and cannot replicate your problem?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,078

    Re: Problem with Conditional Formatting when table is sorted

    With the sample data you could use
    =INDEX($B$5:$B$7,MATCH(I5,$C$5:$C$7,0))=3
    for Northwest

  6. #6
    Registered User
    Join Date
    08-09-2013
    Location
    Columbia, MD
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Problem with Conditional Formatting when table is sorted

    FDibbens - Based on your response (thank you) it sounds like you are sorting the WEEKLY GOLF RESULTS. My issue occurs when I sort the COURSE TABLE. I want "Laytonsville" to remain green in the WEEKLY GOLF RESULTS when the COURSE TABLE is sorted.

    Fluff13 - I will give your suggestion a try - thanks!

    Rick

+ 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] Conditional Formatting Problem in Pivot Table
    By ChristianJ in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-17-2018, 07:16 AM
  2. Conditional Formatting in Excel Table - new row problem
    By GreenBoy in forum Excel General
    Replies: 2
    Last Post: 06-14-2017, 03:51 PM
  3. [SOLVED] VBA code, select conditional formatting content and transpose Sorted to other sheet
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 11-29-2014, 10:00 PM
  4. [SOLVED] Conditional Formatting and protection problem with a Table in Excel 2010
    By Gary Lockton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2012, 12:53 PM
  5. Excel Sorting Problem- need a table sorted
    By nemonat in forum Excel General
    Replies: 2
    Last Post: 01-03-2008, 10:01 AM
  6. Conditional Formatting - Drawing Lines Between Sorted Groups
    By Sam via OfficeKB.com in forum Excel General
    Replies: 2
    Last Post: 05-26-2006, 10:10 AM
  7. [SOLVED] Conditional formatting not getting sorted
    By Lon Sarnoff in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-14-2006, 01:25 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