Fill color in row if data is not available sheet 1 [Solved]

Report
Posts
20
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 12, 2020
-
Posts
20
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 12, 2020
-
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

3 replies

Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
20
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 12, 2020

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
Posts
2665
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 12, 2020
445
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
20
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
October 12, 2020

Thank you so much!