Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'll have to apply the formatting in the code.

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV' post='795096 View Post
    (But it would be a lot easier NOT to use such formatting)
    Not ideal I know, added features that become a pain!

    [quote name='HansV' post='795097' date='27-Sep-2009 10:45']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
    [/quote]

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

    Much appreciated, Thanks!

Posting Permissions

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