kilian
11-09-2015, 03:28 AM
Hello everyone,
On one of my maccro on Excel I'm trying the code below that is not working. I have two columns that are almost the same in two different sheets. I want to highlight their differences with specific criterion. Everything is working well until what's in red in the code, and I see no reason why... There is no error in the code, and the red part is just not doing anything when I run the whole thing.
Thank you for helping
Cheers
Sub differences()
Dim nbRow1 As Integer
Dim nbRow2 As Integer
nbRow1 = Application.WorksheetFunction.CountA(ActiveSheet.Range("$A:$A"))
nbRow2 = Application.WorksheetFunction.CountA(Worksheets(ActiveSheet.Index + 1).Range("$A:$A"))
Set rng1 = Range("A" & 14 & ":A" & nbRow1 + 1)
Set rng2 = Worksheets(ActiveSheet.Index + 1).Range("A" & 14 & ":A" & nbRow2 + 1)
Set rngAll = Range("A" & 14 & ":Z" & nbRow1 + 1)
For Each cell In rng1
If IsError(Application.Match(cell.Value, rng2, 0)) Then
cell.Interior.ColorIndex = 15
End If
Next
For Each cell In rng2
If IsError(Application.Match(cell.Value, rng1, 0)) Then
cell.Interior.ColorIndex = 15
ElseIf "Application.VLookup(cell.Value, rngAll, 14, FALSE)" = "CLOSED" Then
cell.Interior.ColorIndex = 15
ElseIf "Application.VLookup(cell.Value, rngAll, 14, FALSE)" = "COMPLETED" Then
cell.Interior.ColorIndex = 15
End If
Next
End Sub
On one of my maccro on Excel I'm trying the code below that is not working. I have two columns that are almost the same in two different sheets. I want to highlight their differences with specific criterion. Everything is working well until what's in red in the code, and I see no reason why... There is no error in the code, and the red part is just not doing anything when I run the whole thing.
Thank you for helping
Cheers
Sub differences()
Dim nbRow1 As Integer
Dim nbRow2 As Integer
nbRow1 = Application.WorksheetFunction.CountA(ActiveSheet.Range("$A:$A"))
nbRow2 = Application.WorksheetFunction.CountA(Worksheets(ActiveSheet.Index + 1).Range("$A:$A"))
Set rng1 = Range("A" & 14 & ":A" & nbRow1 + 1)
Set rng2 = Worksheets(ActiveSheet.Index + 1).Range("A" & 14 & ":A" & nbRow2 + 1)
Set rngAll = Range("A" & 14 & ":Z" & nbRow1 + 1)
For Each cell In rng1
If IsError(Application.Match(cell.Value, rng2, 0)) Then
cell.Interior.ColorIndex = 15
End If
Next
For Each cell In rng2
If IsError(Application.Match(cell.Value, rng1, 0)) Then
cell.Interior.ColorIndex = 15
ElseIf "Application.VLookup(cell.Value, rngAll, 14, FALSE)" = "CLOSED" Then
cell.Interior.ColorIndex = 15
ElseIf "Application.VLookup(cell.Value, rngAll, 14, FALSE)" = "COMPLETED" Then
cell.Interior.ColorIndex = 15
End If
Next
End Sub