Fill color in row if data is not available sheet 1
Solved/Closed
Raj_1562
Posts
31
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 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Oct 12, 2020 at 09:50 PM
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Oct 12, 2020 at 09:50 PM
Related:
- Fill color in row if data is not available sheet 1
- Fnaf 1 download pc - Download - Horror
- Gta 1 download - Download - Action and adventure
- Fnia 1 - Download - Adult games
- Igi 1 download - Download - Shooters
- Little alchemy cheat sheet - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 16, 2023
549
Oct 8, 2020 at 11:44 AM
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:
Best regards,
Trowa
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 16, 2023
549
Oct 12, 2020 at 11:48 AM
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:
Best regards,
Trowa
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
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 29, 2022
Oct 12, 2020 at 09:50 PM
Oct 12, 2020 at 09:50 PM
Thank you so much!
Updated on Oct 8, 2020 at 01:06 PM
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