NotSoTechSavvy
New Member
- Joined
- Jun 4, 2021
- Messages
- 1
- Office Version
- 2019
- Platform
- Windows
Good Day All,
I need some assistance with code that JLGWhiz wrote to complete my task. The task is to select and highlight cells (highlight rows if they are new rows or deleted rows). The code that I copied from JLGWhiz completes 2/3 of my needs, in which it highlights all cells in which there are discrepancies from sheet1 to sheet2, but also recognizes and highlights new rows that have been added to the sheets. However; the problem arises when a row that was in sheet1 is no longer on sheet2, it does not create the row and highlight the missing data.
Please see attached image. Item #F16 & F16.1 are noted on the first sheet, but when F16 was revised on the second it didn't highlight F16.1 as being a deleted item number. Can someone please assist in revising this code to highlight these changes.
Code:
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, c As Range, fn As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
For Each c In sh1.Range("A2:A" & lr)
If c <> "" Then
Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
For i = 2 To 12
If sh1.Cells(c.Row, i) <> sh2.Cells(fn.Row, i) Then
sh2.Cells(fn.Row, i).Interior.Color = vbYellow
End If
Next
End If
End If
Next
For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
If Application.CountIf(sh1.Range("A:A"), c.Value) = 0 Then
c.EntireRow.Interior.Color = vbYellow
End If
Next
End Sub
Thanks you in advance for your assistance.
I need some assistance with code that JLGWhiz wrote to complete my task. The task is to select and highlight cells (highlight rows if they are new rows or deleted rows). The code that I copied from JLGWhiz completes 2/3 of my needs, in which it highlights all cells in which there are discrepancies from sheet1 to sheet2, but also recognizes and highlights new rows that have been added to the sheets. However; the problem arises when a row that was in sheet1 is no longer on sheet2, it does not create the row and highlight the missing data.
Please see attached image. Item #F16 & F16.1 are noted on the first sheet, but when F16 was revised on the second it didn't highlight F16.1 as being a deleted item number. Can someone please assist in revising this code to highlight these changes.
Code:
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, c As Range, fn As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
For Each c In sh1.Range("A2:A" & lr)
If c <> "" Then
Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
For i = 2 To 12
If sh1.Cells(c.Row, i) <> sh2.Cells(fn.Row, i) Then
sh2.Cells(fn.Row, i).Interior.Color = vbYellow
End If
Next
End If
End If
Next
For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
If Application.CountIf(sh1.Range("A:A"), c.Value) = 0 Then
c.EntireRow.Interior.Color = vbYellow
End If
Next
End Sub
Thanks you in advance for your assistance.