+ Reply to Thread
Results 1 to 5 of 5

Filter without having the colors change

  1. #1
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    19

    Filter without having the colors change

    Hi all,

    Not sure if this is possible but I have a sheet which is is broken up by datasets in 5 different colors. Every month the numbers will change which will cause some data to drop into a different color area. I presently have a Rank formula in the sheet but next month when I re rank the sheet some data may now be in a different color area. The question is, is there a way to lock in the background color so even if I filter a name or data only the data changes and not the color? I have attached a screenshot so it may help with what I am talking bout. Thanks all!
    Attached Images Attached Images

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,604

    Re: Filter without having the colors change

    I'm having a hard time envisioning what changes you make to the data that could be causing the colors to change. If you just filter, the colors won't change. That is, if you filter on a name (no names are visible in your screenshot but you mention names), those names will still have the same colors they did before you filtered them.

    If the rank changes and you re-sort based on rank, then yes, the colors would change. One way to deal with that is to use conditional formatting for the colors. You would need one conditional formatting rule for each color section. For example you would set up a rule having a yellow fill color for A:C (or whatever your columns are):

    =AND(2<=ROW(A1),ROW(A1)<=17)

    This will always make rows 2:17 yellow no matter how you sort.

    It is not clear what you want this to look like when you filter.

    It would save time if you could provide your file. An image attachment has limited value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    19

    Re: Filter without having the colors change

    I tried attaching a sample, but yes i feel like conditional formatting is the best route. for example If someone goes from rank 24 to 25 they would change colors but I want it so i can sort them with there new rankings and the colors stay intact.
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,604

    Re: Filter without having the colors change

    Your actual file is more complicated than what is suggested by your screenshot. You have labels in the left column giving top 10% and quartiles, but they don't seem lined up quite right. Will you always have the same number of rows?

    The solution I attached assumes the number of rows is always the same. If not, you will have to adjust the formulas to count the rows and calculate how many is 10% and 20%.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-23-2018
    Location
    Houston, Texas
    MS-Off Ver
    2013 pro plus
    Posts
    19

    Re: Filter without having the colors change

    Thanks, this is what I needed. I can play with the formulas as you said if we had or delete dealers. Appreciate your quick help with this!

+ 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] VBA code to change Excel chart bar colors as per cell colors
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-21-2016, 03:29 AM
  2. Replies: 3
    Last Post: 10-21-2015, 03:51 PM
  3. Worksheet On Change Calls Sub to change Cell colors
    By sportsguy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2014, 12:20 PM
  4. Need to filter by two colors
    By aljiwani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2013, 09:38 PM
  5. [SOLVED] Filter by all colors
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-30-2013, 02:57 PM
  6. [SOLVED] Filter by Consecutive Cell Values or Colors
    By thelegazy in forum Excel General
    Replies: 10
    Last Post: 07-26-2013, 12:20 PM
  7. changing filter arrow colors
    By Tonka_Joe_Kiwi in forum Excel General
    Replies: 0
    Last Post: 11-19-2005, 12:30 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