Delete rows ONLY IF duplicate value in column A + cell is highlighted Specific Color.

[Zlerp]

Hello All,

I am looking for a Macro that will look thorugh a Sheet called "New Property" (may not be active sheet at time) in column A for Duplicate values. If there is a duplicate value and the cell color is Pink RGB(255, 0, 255) , Then i want that entire row deleted.


The Row is deleted only if the following Criterias are BOTH met:
1. There is a Duplicate Value in Column A
2. Cell color in Column A is Pink RGB(255, 0, 255)


Thanks you for your time and Help. Any input is greatly appreciated!
Zlerp

[Jacob Hilderbrand]

You can do this to get started. First we want to check for a duplicate which we can use a CountIf formula on, second you want to check the RGB color of the cell.
[vba]
With Sheets("New Property")
For i = 100 To 2 Step -1 'Change to correct start and end row variables.
'Test for duplicate
If Application.WorksheetFunction.CountIf(.Range("A2:A" & i),"=" & .Range("A" & i).Text)>1 Then
'Test for color
If .Range("A" & i).Interior.Color = RGB(255,0,255) Then
.Range("A" & i).EntireRow.Delete
End If
End If
next i
End With
[/vba]

If you need the font color instead of the fill color just change Interior to Font.

[Zlerp]

Hey Jacob,

This works Perfectly!! Thank you for the help and the explanation of why you wrote what you did!

You can do this to get started. First we want to check for a duplicate which we can use a CountIf formula on, second you want to check the RGB color of the cell.
[vba]
With Sheets("New Property")
For i = 100 To 2 Step -1 'Change to correct start and end row variables.
'Test for duplicate
If Application.WorksheetFunction.CountIf(.Range("A2:A" & i),"=" & .Range("A" & i).Text)>1 Then
'Test for color
If .Range("A" & i).Interior.Color = RGB(255,0,255) Then
.Range("A" & i).EntireRow.Delete
End If
End If
next i
End With
[/vba]

If you need the font color instead of the fill color just change Interior to Font.

Thanks again!
Zlerp