Fill color in row if data is not available sheet 1

Solved/Closed
Raj_1562 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Updated on Oct 8, 2020 at 08:38 AM
Raj_1562 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Oct 12, 2020 at 09:50 PM
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
Related:

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 8, 2020 at 11:44 AM
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
Raj_1562 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Oct 8, 2020 at 01:06 PM
Hi Trowa,

Hope you are doing good!

Wow, amazing! The code is working fine.

Needed one help from you for a small correction after entering the correct name (2nd Image- Name column) in highlight cell the colour needs to change (AA maintained in sheet 2) is this possible? and the background colour is not cleared.



I have pasted the code in Worksheet change to auto-update color in sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Sub RunMe()
Dim mFind As Range


Thanks,
Raj
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 12, 2020 at 11:48 AM
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 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Oct 12, 2020 at 09:50 PM
Thank you so much!