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.
lastcol= recon.cells(1,recon.columns.Count).End(xlup).column
is wrong. To return correctly it should belastcol= recon.cells(1, recon.columns.Count).End(xlToLeft).column
.Sortfield.Clear