Script for sorting

[Gor_yee]

hi all,

I need some help...

I would like to have a script that can do some sorting for me

Col A - Region
Col B - Area
Col C - Store
Col D - Trading Department
Col E - Fine Department
Col F - Reference Number
Col G - Item Description
Col H - Base FC
Col I - Uplift
Col J - Demand
Col K - Display
Col L - Planning

First of all, I would like to create a button that will allow me to
sort the Col F and Col G in a ascending order, then on col L
(planning), it would show me if the store has done any planning,
basically it would be using the following forumala

=IF(I2=J2,"Unreviewed","Reviewed")

Also, can I fill the ones that say "Unreviewed" with a color (yellow)

sorry if i am asking too much...hope someone can help me...

 

[squenson via OfficeKB.com]

You are not asking for too much! Forgive me if I give you too detailed
instructions, I do not know your proficiency level with macros and coding...

1. Automatic sorting
There is a feature called "Record macro" in the menu Tools > Macro that
allows you to record in a macro what you have typed on the keyboard. If I
select the range A1:L22, click on the menu Data > Sort, then select column F,
G and L, with a header row, then stop macro recording; then I press ALT+F11
to open the visual basic editor, then open the Module1 on the left pane, I
get:

Sub Macro1()
' Macro1 Macro
' Macro recorded 04-08-2007 by SQ
Range("A1:L13").Sort Key1:=Range("F2"), _
Order1:=xlAscending, Key2:=Range( _
"G2"), Order2:=xlAscending, _
Key3:=Range("L2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End Sub

So each time I will execute Macro1, I will sort the range accordingly. I
suggest that you modify the range and put a larger one (something like "A1:
Z5000") in order to accommodate growth of your spreadsheet.

Now, call the toolbar for the button (menu View > Toolbar > Control Toolbox).
Click on the button icon and draw the button (click down and make a rectangle
while still pressing the left mouse button down) where you want it. Right-
click on the button and select Properties. In the new Window, change the
caption field into "Sort Me!" (without quote), or whatever text you like. Now,
go back to the Visual Basic window, click on the sheet Sheet1 in the left
pane, then select "CommandButton1" in the first combo box. Automatically,
Excel will create some code:

Private Sub CommandButton1_Click()

End Sub

In between these two lines, simply type Macro1. This means that each type
that the button is clicked, the program macro1 will execute. As macro1 is the
program that sorts the sheet, you are (almost) done!

Go back to the spreadsheet and click on the button to test it... but nothing
happens. This is because you are still in Edit mode of the button, so you
should exit it by clicking the top left icon on the toolbar Control Toolbox
(with a square, ruler and pen). Now, if you click on the "Sort Me!" button,
the spreadsheet is sorted.

2. Yellow cell
For automatic formatting, use conditional formatting. For that, select entire
column L, then click on the menu Format > Conditional Formatting. On the new
window, select or type "Cell Value Is", "Equal To", "Unreviewed" (all without
quotes), then click on format button, select the third tab "Pattern", then
choose the yellow color, and confirm.

You can also use the autofilter option (select the first row, then menu Data

Filter > Autofilter), then selecting "Unreviewed" in column L will only

show you these lines, useful for printing for example.

Hope this helps,
Stephane Quenson.