Results 1 to 4 of 4

Thread: OT-Excel 2007 question

  1. #1
    Join Date
    Feb 2003
    Location
    Mtl, Canada
    Posts
    2,379

    OT-Excel 2007 question

    I want to have a table where 1 of the columns will contain a number between 1 and 730 and when the value in this cell is less than or equal to 30 the cell will change color to red(this is done) however i want the row to then rise to the top of the list by itself so every time I open the table I can readily see which rows have a 30 or less in the cell. Is something like this even possible?

    thx

    chuck

  2. #2
    Join Date
    Aug 2006
    Location
    Saint Helens, OR
    Posts
    2,463
    The quickest, easiest way would be set a custom filter on the column. Show everything less than 31. (or equal to or less than 30).

    As for moving a row based on conditional formatting or a specific data range, I have no idea.
    Measure twice, cut three times, start over. Repeat as necessary.

  3. #3
    Join Date
    Jun 2007
    Location
    Aurora, IL
    Posts
    161
    The only way to move the row to the top of the worksheet is with VBA in a macro. It's not particularly difficult if you know VBA. If you don't, it's magic. Send me a PM if you want more info.
    Dave

    Nothing is idiot-proof for a sufficiently ingenious idiot!

  4. #4
    Conditional formatting will take care of your cell color changes for those less than 30. There are a few ways to do the sort. The easiest is to have the number column you want to sort on as the left most column. Click on that column letter at the top of the sheet and drag to the right until all data columns are selected, then click the sort&filter a-z button. If you want to get fancy, you can record a macro from the view ribbon. It will act as a key logger and will replicate everything you do to the sheet between record and stop. So, record a new macro, do your select and sort, stop recording. Then select macros, view macros, select the one you just created, click step into, and change the name at the top from Sub macro_1 (or whatever the macro was named) to Auto_Open. Whenever the sheet is opened, you'll probably be prompted to allow the macro. If you do, it will sort your data. Presto.
    Last edited by Ben Hatcher; 02-29-2012 at 1:35 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •