Formatting problem when sorting

Topic

New Reply

I am using the following code:

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Static iCol As Integer, iSrt As Integer
Dim w As Long
Dim x As Range

If Target.Column > 18 Or Target.Row > 4 Then
	Exit Sub
End If

	Cancel = True

	w = Cells(Rows.Count, 2).End(xlUp).Row
	Set x = Rows("5:" & w)

	If (Target.Column = iCol) And (iSrt = xlAscending) Then
		iSrt = xlDescending
	Else
		iSrt = xlAscending
		iCol = Target.Column
	End If

	x.Sort Key1:=Selection, Order1:=iSrt, Header:=xlNo, OrderCustom:=1, _
	MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub

This works great for me apart from one small problem. My data is formatted as:

Row 5 – Font 18, Row 6 – Font 17,………. Row 10 – Font 13, Row 11 and all rows thereafter Font 12. To accomodate the larger font in rows 5:10, the row height will be proportionately larger.

Regardless of how the data is sorted, I want the formatting and the row heights to stay as is. Any thoughts on how I can achieve this?

Reply | Quote

Replies

You’ll have to apply the formatting in the code.

(But it would be a lot easier NOT to use such formatting)

Reply | Quote

Here is some sample code:

Code:

  Dim r As Long
  For r = 5 To 10
	Rows(r).Font.Size = 23 - r
  Next r
  Rows("11:" & w).Font.Size = 12
  Rows("5:" & w).AutoFit

Reply | Quote

(But it would be a lot easier NOT to use such formatting)

Not ideal I know, added features that become a pain!

Here is some sample code:

Code:

  Dim r As Long
  For r = 5 To 10
	  Rows(r).Font.Size = 23 - r
  Next r
  Rows("11:" & w).Font.Size = 12
  Rows("5:" & w).AutoFit

Clever! My half written code did’nt look like that.

Much appreciated, Thanks!

Reply | Quote