Sort data and leave one specific cell with $ dollar sign formatting

  • Thread starter Thread starter mary
  • Start date Start date
M

mary

In Excel 2003, I have revenue numbers going from b2 to b100. I want to
make sure B2 always has a dollars sign, comma, and two decimal places
(example: $5,348.25). All other cells have same formatting but not the
dollar sign. I want to sort in ascending or descending and B2's format
never change regardless of what number is in it. Tried several
conditional formatting with IF statement and Text function but could
not get it to work.
 
In Excel 2003, I have revenue numbers going from b2 to b100. I want to
make sure B2 always has a dollars sign, comma, and two decimal places
(example: $5,348.25). All other cells have same formatting but not the
dollar sign. I want to sort in ascending or descending and B2's format
never change regardless of what number is in it. Tried several
conditional formatting with IF statement and Text function but could
not get it to work.
Click to expand...

You could add a button with following code behind it:

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Range("b2").NumberFormat = "#,##0.00"
Range("b2:b20").Sort Key1:=Worksheets("Sheet1").Range("b1")
Worksheets("Sheet1").Range("b2").Style = "Currency"

End Sub

I take it when you start the cells B3:B100 already have the format #,##0.00
Otherwise you have to add a line like:

Worksheets("Sheet1").Range("b3:b100").NumberFormat = "#,##0.00"
 
Would you be willing to use a "helper" column to *display* what you're
looking for.

Since I don't know what you're using Column B for, this suggestion may be in
"left field".

Say in C2 you create your $ format.
In C3 to C100 create your number format.

Then, in C2 enter this formula:

=SMALL($B$2:$B$100,ROW(A1))

Now, you must copy this formula down Column C, BUT ... since you *don't*
want to copy the format ($'s),
Right click in C2, choose "Copy",
Select C3 to C100,
Right click in the selection and choose "Paste Special",
Click on "Formulas", then <OK>.

This procedure will *automatically* display sorted (ascending) numbers
*instantly*, as you add or change numbers in Column B, and *retain* the
pre-set formatting.

Now, I mentioned that "Paste Special" copy procedure above (you could have
more easily formatted *after* copying the formulas), because you will have
to follow the same procedure to revise the formula to return a descending
sort.

Revise the formula in C2 to return a descending sort, OR ... you could
assign another column to sort descending, using this revision:

=LARGE($B$2:$B$100,ROW(A1))
 
The only way I know how to do what you want would be to sort by macro. That
is, write a macro to do your sorting for you. Then you can include code
within that macro to redo the formatting every time it sorts, to get the
formatting you want.
The following 3 macros do that. You could have a button on your sheet to
execute the first macro for ascending sort and another button to execute the
second macro for Descending sort. Both macros call the third macro to do
the reformatting for you.
I am sending you a small file with all 3 macros and the buttons on the
sheet.
HTH Otto
Sub SortDataAsc()
Dim RngToSort As Range
Set RngToSort = Range("B2", Range("B" & Rows.Count).End(xlUp))
RngToSort.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Call ReFormat
End Sub

Sub SortDataDesc()
Dim RngToSort As Range
Set RngToSort = Range("B2", Range("B" & Rows.Count).End(xlUp))
RngToSort.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Call ReFormat
End Sub

Sub ReFormat()
[B2].NumberFormat = "$#,##0.00"
Range("B3", Range("B" & Rows.Count).End(xlUp)).NumberFormat = "#,##0.00"
End Sub
 
Back
Top