Previous Topic Next Topic

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

Answer 1 / 3
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
Answer 2 / 3
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
Answer 3 / 3
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!

Similar discussions

How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses