Solved: Sort Not Working When Formatting Applied

[Opv]

Attached is a sample worksheet that includes two scripts. One of the scripts is for the Worksheet("Dictionary") and the other is a sort routine in its own Module.

Something (apparently unrelated to the range setting) in the "Worksheet(Dictionary") sheet is preventing the sort from including the first row of data in the sort routine. Whatever it is is also preventing me from cutting a row and pasting it to the end of the data rows.

As strange as it sounds (to me anyway), the problem appears to be in the temporary formatting that is applied to the range of data to highlight the cells for the active row. The reason I suggest this is that I can copy the data to another sheet, remove all the formatting and the sort seems to work fine.

Can someone please help me identify what it is causing the behavior described above?

Thanks,

Opv

[Paul_Hossler]

I think your Selection_Change on Dictionary was the problem

I disabled Events around the sort, and a couple of other things.

Try it now

[vba]

Option Explicit
Sub sortRows()
Dim myRows As Integer
Dim myCols As Integer
Dim myRange As Variant

Worksheets("Dictionary").Activate

'added
Application.EnableEvents = False

myRows = Range("A4", Range("A4").End(xlDown)).Count
myCols = Range("$A4:C4").Column

'changed Key1 to A4, and Header = xlNo (might be 2007 only)
Range("A4:C" & myRows + 3 & "").Sort Key1:=Range("A4"), Order1:=xlAscending, _
Header:=xlNo, MatchCase:=False, Orientation:=xlTopToBottom

'added
Application.EnableEvents = True
End Sub

[/vba]

and

[vba]

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Data As Range
Dim i As Integer
Dim k As Long 'ActiveCell

i = 1
k = ActiveCell.Column()
Set Data = Range("A4:C10000")

If ActiveCell.Row <= 3 Then
Exit Sub
End If

'added
Application.ScreenUpdating = False

Data.Interior.ColorIndex = xlNone
Data.Font.Bold = False
Data.Font.ColorIndex = 0
Data.Font.Size = 11
Data.RowHeight = 13

With ActiveCell
.Offset(0, -(k - i)).Resize(1, 3).Interior.ColorIndex = 1
.Offset(0, -(k - 1)).Resize(1, 3).Font.Bold = True
.Offset(0, -(k - 1)).Resize(1, 3).Font.ColorIndex = 2
.Offset(0, -(k - 1)).Resize(1, 3).Font.Size = 14
.Offset(0, -(k - 1)).Resize(1, 3).RowHeight = 20
End With

'added
Application.ScreenUpdating = True

End Sub

[/vba]

By the way, the Selection_Change Target is the range that is now selected, so you could probably simplfy the code a bit

Paul

[Opv]

I think your Selection_Change on Dictionary was the problem

I disabled Events around the sort, and a couple of other things.

Try it now

Paul

Thanks. That did the trick.

Opv