OT-Excel 2007 question

[Chuck Wintle]

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

[Greg Peterson]

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.

[David Dockstader]

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.

[Ben Hatcher]

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.