+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting Not Moving When Sorting

  1. #1
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Question Conditional Formatting Not Moving When Sorting

    Hey Everyone,

    I've been developing a database for my band, so we can keep track of our songs quality level and what we should practice, and so we can quickly look when we need to set songs for gigs (this hasn't had data entered our songs are not all on the lowest quality :D )


    I've struggled through the coding of this database and i am finally stumped and just over it.


    On the main Database page, i want to be able to sort via alphabetical, colour, ect. On each song has conditional formating, which changes colour depending on the level the song is at.

    What should happen is when i sort it, the song name moves and so does the condtional formating accordingly

    What is happening is the song name moves but the conditional formating stays where it is. Which means the colours are updating for the wrong song

    I have attached the file to this.

    Any help would be appreicated


    Thanks

    Bensley
    Attached Files Attached Files
    Last edited by Bensley; 05-07-2018 at 09:37 PM.

  2. #2
    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,917

    Re: Conditional Formatting Not Moving When Sorting

    I think the problem lies with your CF formula. The INDEX range is a single cell - try making it the full data range
    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

  3. #3
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Conditional Formatting Not Moving When Sorting

    Could you give me an example of that, I am not sure exactly what range you are refering to

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Conditional Formatting Not Moving When Sorting

    You have many problems here. I think we are going to have to tear it down and start over again.

    Solution

    I think you want your CF formulas on Database to look like this:

    =VLOOKUP($A1,Brains!$A$5:$D$,4,FALSE)=-1

    For each color you want to apply (the first 14 rules) change them to look like that except change the -1 to whatever is appropriate. Then change the "Applies To" range for each rule to $A$5:$P$99 or some other large number. Then delete all the rules after the first 14.

    Diagnosis

    =COUNTIF(INDEX(Brains!$D$37,,ROW($A$1)),"=-1")

    As Ford mentioned, the INDEX reference uses a single cell as the range. Instead of Brains!$D$37 I think you intended Brains!$D$5:$D$99 or something similar.

    The INDEX reference then omits the row index. I don't even know what happens when that is omitted, but it probably defaults to 1.

    Then it uses ROW($A$1) as the column index, which is always going to be 1, which means this INDEX formula will always return the value in Brains!$D$37.

    Aside from that, I do not see why this formula uses COUNTIF at all, and I do not see the reason for using INDEX.

    The same set of 14 rules is repeated over and over and over again, once for each row. When you find that you are doing the same thing over and over again, it is a clue that there is a better way to do it. The correct way to do this is to have one set of rules that applies to all rows.

    It appears that you want your formula to take the song on the Database sheet, then find that song on the Brains sheet, then find the "Hard Value" for that song, and then use that value to determine what the color should be on the Database sheet. But your formula doesn't do anything like that.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Conditional Formatting Not Moving When Sorting

    The formula comes from another thing i did, which i got help with on here as well, i was only starting then, and it was working so i just went with it.


    At the moment when the song is originally added it is set to look at the hard value and see what the value is then set colour accordingly, but when it is sorted, sorting moves the value on database but not the conditional value


    But what you're saying is have it look at what is in A31 for example, which might be 'All Your Reasons' and find it in Brains, which might be A3 then continue with using 14 or so formulas for each number that it could be?


    Am i on the right track with your thinking ?

    and if so would me replacing the -1 code with

    Please Login or Register  to view this content.

    will fix the problem, and then all the others, for each song? or is there other things else where.




    Bensley

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Conditional Formatting Not Moving When Sorting

    To clarify this I did it for you on Database, and also Brains where you have the same problem. I did not look at the other other sheets.


    I played about a dozen of these songs when I was in high school--over 40 years ago. I'm pleasantly surprised people are still playing them. Do you do Blind Eye by Uriah Heep or Wishbone Ash? (P.S. Chior Girl should be Choir Girl)

  7. #7
    Registered User
    Join Date
    03-19-2018
    Location
    Goulburn
    MS-Off Ver
    MS Office Professional Plus 2016
    Posts
    35

    Re: Conditional Formatting Not Moving When Sorting

    Thanks so much 6StringJazzer, Worked perfectly, means I can get rid of the formatting I had in VBA that was being set every time I added a song


    hahaha you can't go wrong with the classics, Dad is our manager at the moment, he chooses the songs, he hasn't lead us wrong yet. Plus so much more fun to play.
    Blind Eye was added in for Australia Day, Hunters and Collectors that is, quirky but a good quirk

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Conditional Formatting Not Moving When Sorting

    I haven't heard of Hunters & Collectors, I guess they didn't get airplay in the U.S. I guess "Blind Eye" is a popular song name Here's the song I played. Wishbone Ash was known for harmonized guitar lines.

    Nice database. You guys must have been working hard to establish a list that long.

+ 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. Moving data to other columns from conditional formatting
    By hurky12 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-01-2017, 03:43 PM
  2. Replies: 2
    Last Post: 06-09-2014, 03:58 PM
  3. [SOLVED] Gantt Chart Moving Bar with Criteria Conditional Formatting
    By rickyilas in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-30-2012, 03:37 PM
  4. Replies: 2
    Last Post: 05-12-2012, 01:59 AM
  5. Conditional Formatting and Moving a Trend Line
    By davidswift in forum Excel General
    Replies: 0
    Last Post: 06-04-2010, 06:14 AM
  6. Conditional Formatting with a moving Range
    By Shytott in forum Excel General
    Replies: 9
    Last Post: 01-18-2010, 01:31 PM
  7. help to make moving conditional formatting
    By birkhe in forum Excel General
    Replies: 0
    Last Post: 05-19-2009, 05:25 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