I’m importing CSV text files of variable length, generated by a third party application, into an Excel sheet. Because the users of the sheet are not very familiar with Excel, I created a toolbar with a button for each column to sort by. These buttons set a global variable strSortedBy to the column to sort by (A-F). Then there’s a button that calls a Sub to mark, in red, “double” rows that have the same value in the cells in the sorted by column. This works, but I would like to be able to unmark by clicking on the same button again. So I added the line lngColor = 3 – lngColor, but that fails; the rows stay red. What am I doing wrong?
Public strSortedBy As String
Sub MarkDouble()
Dim lngColor As Long
Dim lngRow As Long
lngColor = 3 – lngColor ‘ Toggle between red (3) and black (0)
For lngRow = Range(strSortedBy & “65536”).End(xlUp).Row To 2 Step -1
If Range(strSortedBy & lngRow) = Range(strSortedBy & (lngRow – 1)) Then
Range(strSortedBy & lngRow).EntireRow.Font.ColorIndex = lngColor
Range(strSortedBy & (lngRow – 1)).EntireRow.Font.ColorIndex = lngColor
End If
Next lngRow
End Sub
I have a second issue. When a text file is imported, keying Ctrl+End should jump to the last cell in the sheet that contains data, i.e. F3229. But instead it jumps to i.e. BT3229 for some reason unknown to me. Clearing the sheet and importing another file the last cell is i.e. F2886, but Ctrl+End still jumps to, in this case, BT3229. Saving the now empty sheet before importing the next file doesn’t help. What is causing this and how can it be “reset” to jump to the right location?
