Fill color in row if data is not available sheet 1 Solved/Closed

Question

Dear Experts, 

In Excel, I have maintained "NAME" and "CODE" lists in Sheet 2. When pasting (copied data from another source) the"NAME" and "CODE" on sheet 1. I would like to check the "NAME" and "CODE" on sheet 2 and if the "NAME" and "CODE" is not available on sheet 1 (which is maintained in sheet 2). The row I would like to fill some colour.

Please find the file link: https://we.tl/t-qluH00BNiU



Thanks in advance!

Regards,
Raj

TrowaD · Answer

Hi Raj,

Thanks for the screengrabs. They really help to understand what you are looking for.

Since you didn't mind the color, I went for Burly Wood :). I like color names more the color numbering. If you want to try a different color, remove BurlyWood from code line 11 and leave the rgb, then hit Ctrl+Space to get a list to choose from.

Here is the code:
Sub RunMe()
Dim mFind As Range

Sheets("Sheet1").Select

For Each cell In Range("D4:D" & Range("D4").End(xlDown).Row)
    Set mFind = Sheets("Sheet2").Range("C4:C11").Find(cell.Value)
        If Not mFind Is Nothing Then
            If cell.Offset(0, 1) = mFind.Offset(0, 1) Then GoTo SkipColor
        End If
    Range(Cells(cell.Row, "B"), Cells(cell.Row, "E")).Interior.Color = rgbBurlyWood
SkipColor:
Next cell
End Sub

Best regards,
Trowa

TrowaD · Answer

Hi Raj,

I'm doing good, thanks.

I changed the code to run automatically, when a change is made in sheet2 as well as updating the color back to white:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mFind As Range

With Sheets("Sheet1")
    For Each cell In .Range("D4:D" & Range("D4").End(xlDown).Row)
        Set mFind = Range("C4:C11").Find(cell.Value)
            If Not mFind Is Nothing Then
                If cell.Offset(0, 1) = mFind.Offset(0, 1) Then
                    .Range(.Cells(cell.Row, "B"), .Cells(cell.Row, "E")).Interior.Color = rgbWhite
                    GoTo SkipColor
                End If
            End If
        .Range(.Cells(cell.Row, "B"), .Cells(cell.Row, "E")).Interior.Color = rgbBurlyWood
SkipColor:
    Next cell
End With
End Sub

Best regards,
Trowa

Raj_1562 · Answer

Thank you so much!

Fill color in row if data is not available sheet 1

3 responses

Similar discussions

Subscribe To Our Newsletter!