Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Cell Border Not Moving With Cell When Sorted

  1. #1
    Join Date
    6th December 2006
    Posts
    5

    Cell Border Not Moving With Cell When Sorted

    Hi,

    can anyone advise how to filter rows of data, in ascending or descending order, whilst still retaining formatting (in my case cell borders) and conditional formatting?

    At present, when I filter rows in ascending / descending order, the cell formatting & conditional formatting stays in it's original position, rather than moving with the cell. I can't find a way to resolve this.

    Any help would be much appreciated!

    Thanks

    James
    Last edited by Dave Hawley; December 7th, 2006 at 12:10.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th October 2006
    Posts
    20

    Re: Retaining Formatting In Filtered Cells

    Are you using autofilter or advanced filter?

    Autofiltering simply hides rows that don't apply to your filter, whereas advanced filtering creates a new table.

    Excel should keep the cell format in autofilter, but can't in advanced filter mode. (I'm open to correction on that)
    Last edited by Dave Hawley; December 7th, 2006 at 12:10.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Retaining Formatting In Filtered Cells

    You must be doing something else to cause this. Both filter typ will not effect formatting.

  4. #4
    Join Date
    6th December 2006
    Posts
    5

    Re: Retaining Formatting In Filtered Cells

    Hi,

    thanks for getting back to me. An example:

    Number
    1
    2
    3

    Supposing we put an auotfilter on the list above, and also coloured the cell including the '1' blue, and put a border around that same cell.

    If we then autofilter to sort the cells in descending order:

    Number
    3
    2
    1

    The blue fill would move with the '1', however the border would stay in it's original position, and hence be around the '3'. I'd like the border to move along with the contents of the cell.

    Can you advise how to do this?

    Thanks!

    James

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    3rd October 2006
    Location
    Cardiff, UK
    Posts
    263

    Re: Retaining Formatting In Filtered Cells

    Sounds to me more like a 'Sort' than an autofilter.

    Nothing springs to mind atm. Hmm, it is annoying.

    KJ
    Last edited by Kneejerk; December 7th, 2006 at 21:14.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    6th December 2006
    Posts
    5

    Re: Retaining Formatting In Filtered Cells

    Yes - it is a sort, but it's an option in the autofilter. If you set up an autofilter, 'sort ascending' & 'sort descending' are two of the options.

    It's when I use these that the border formatting stays in it's original position, rather than moving with the cell value. Although colour formatting moves with the value.

    James

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

  8. #8
    Join Date
    6th December 2006
    Posts
    5

    Re: Conditional Format Moves When Sorted

    Hi - I've uploaded an example.

    Thanks,

    James
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Conditional Format Moves When Sorted

    That is odd. Not sure why that happens. However, if you set the border via Conditional Formatting it moves with the cell.

  10. #10
    Join Date
    6th December 2006
    Posts
    5

    Re: Cell Border Not Moving With Cell When Sorted

    Hi,

    the problem is that I have other conditional formats that I want to use (& the limit is 3!). Also, some cells require thick borders and some require thin ones.

    Is there a way to use VBA to link the border to the cell value in the way that the cell colour is?

    Thanks,

    James

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Moving cell value to first empty cell in sheet
    By BenTheBookie in forum EXCEL HELP
    Replies: 2
    Last Post: June 8th, 2006, 01:11
  2. Replies: 2
    Last Post: January 25th, 2006, 07:33
  3. Replies: 7
    Last Post: July 14th, 2005, 05:13
  4. Replies: 3
    Last Post: November 25th, 2004, 11:03
  5. [Solved] active cell border
    By filmrealite in forum EXCEL HELP
    Replies: 7
    Last Post: June 18th, 2003, 01:43

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
  •  
porno