0

picture of worksheet recon that needs to be sort Application.ErrorCheckingOptions.NumberAsText= False

Dim lastRow As Long 
Dim i As Long, j As Long
Dim Tiger, Lion, recon As Worksheet
Dim compareRange As Range
Dim cell As Range
Dim dict As Object

Set recon= Thisworkbook.Workshets("Recon")
lastRow= recon.cells(recon.Rows.Count, "A"). End(xlup).Row
lastcol= recon.cells(1,recon.columns.Count).End(xlup).column

recon.sort.Sortfield.Clear

recon.Range("A1:A" & lastRow).NumberFormat ="@"
With recon.sort
.Sortfields.Clear
.SortField.Add2 Key: = Range("A2:A1401"_
), Sorton: = xlSortOnValues, Order: = xlAscending, DataOption" =_
xlSortTextAsNumbers
End With

With recon.Sort
.SetRange recon.Cells (1,1). CurrentRegion
.header= xlYes
.MatchCase = False
.Orientation= xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Counter = 0
For i = 2 to LastRow
If recon.cells(i,1).Value = " " Then
recon. Range (recon.Cells (i,1), recon.Cells(i, lastcol)). Interior.Color - RGB(255, 182 193)
recon.cells(i,1).Value = "NO ID" + Str(counter)
counter = counter + 1
End If
Next i

Set compareRange = recon.Range("A1").Resize(lastrow,recon.Cells(1,          recon.Columns.Count).End(xlToLeft).Column)'

For i =2 to LastRow

If Application.WorksheetFunction.CountA(recon.Rows(i)) <> 0 Then

For j =2 to recon,cells(i,recon.Columns.Count).End(xlToLeft).Column - 2

If recon.cell(i,j).Value <> recon.cells(Application.Match(recon.cells(i,1).Value,    recon.Colums(1),0,j).Value Then

recon.cells(i,j).Interior.Color = RGB(255,182,193)
recon.cells(Application.Match (recon.cells(i,1).Value, recon.Columns(1),0), j).Interior.Color     =RGB(255,182,193)

End If
Next J
End If
Next i

 'Highlight entire row if single sided identifier
 For Each cell In recon.Range ("A2:A) & lastRow)
 If Application.WorksheetFunction.CountIf(recib.Range("A2:A" 7 lastRow), cell.Value) = 1 Then
 Recon.Range(recon.Cells(cell.Row, 1), recon.Cells(cell.Row, lastcol)).Interior.Color =         RGB(255,182,193)
End If 
If cell.Value = "" Then
recon.Range(recon.Cells(cell.Row, 1), recon.Cells(cell.Row, lastcol)).Interior.Color =         RGB(255,182,193)
End If
Next Cell

Sheets("Recon").Select

Call sortcol

MsgBox "Recon Complete.", , "Tiger Recon"

End Sub

Sub sortcol()

Dim recon, Tiger As Worksheet
Dim Tigerlr,lionlr As Long
Dim lastcol, lastRow As Long 
Dim colName As String, foundCol As Range Long
Dim visiblerange, refrange As Range

Set recon = ThisWorkbook.Sheets("Recon")
Tigerlr = recon.Cells(recon.Rows.Count, "A").End(xlUp).Row
lastcol = recon.Cells(1, 1).End(xlToRight).Column
lastRow = recon.Cells(recon.Rows.Count, "A").End(xlUp).Row

Recon.Sort.SortField.Clear
targetColor = RGB(255, 182, 193)

'Add color sortkey
For col = 1 To lastcol
With recon.Sort.SortFields.Add(Key:=recon.Columns(Col), _
SortOn:=xlSortOnCellColor, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers)
.SortOnValue.Color = targetColor
End With
Next Col

'Perform sort
With recon.Sort
 .SetRange recon.Cells(1,1).CurrentRegion
 .header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
End With

End Sub

Sub clearRecon()
Dim recon As Worksheet
Set recon = ThisWorkbook.Sheets("Recon")
recon.Cells.Clear
recon.Cells.ClearContents
End Sub

I am trying to compare the details between 2 rows using the identifier ClearingID between 2 systems Tiger and Lion. If there is a difference, the cell will be highlighted in red RGB(255, 182 193) and sorted at the top. If there is only 1 entry in either system, the entire row will be highlighted.

However, the below code only seem to be picking up missing clearing ID or missing entry. Different data in cell will not be picked up, nor sorted on the top.

7
  • Without seeing the range you try processing it is difficult to help you, only looking to a piece of cod which does not do what you want... Then, lastcol= recon.cells(1,recon.columns.Count).End(xlup).column is wrong. To return correctly it should be lastcol= recon.cells(1, recon.columns.Count).End(xlToLeft).column.
    – FaneDuru
    Commented Jul 7 at 17:23
  • Does your posted code compile? It seems to have a few typos... Eg: Sortfield.Clear Commented Jul 7 at 17:51
  • Hey, is using VBA a strict requirement here? If you have Microsoft 365, you may be able get what you want with FILTER, SORTBY and conditional formatting.
    – nkalvi
    Commented Jul 7 at 20:01
  • Hi @nkalvi, yes I strictly need to be using VBA requirements. Thanks for your help. Commented Jul 7 at 23:57
  • Hi @TimWilliams, noted. Sorry the long code seems to have some typo. I will re-edit it again. Thanks for the input. Cheers. Commented Jul 7 at 23:59

0

Browse other questions tagged or ask your own question.