Delete filled column based on font color

[Nancy123]

Hi Everyone,
Kindly need some solution regarding my project here.
Here is my VBA code, what I want to do is :
1. If the cells in column A have duplicate values, then I will change the font color from black to red for the duplicate values starting from the second occurance (DONE for the code)
2. For rows which have red font color in column A, I want column E until H in that row to be blank (NEED HELP)
3. Then I will delete the conditional formatting so that all cells in column A will have black font color again (DONE for the code)

Sub SecondOcurrenceFont()
    
'Give red color to Second Ocurrence

Sheet3.Activate

Columns("A:A").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($A$1:A1,A1)>1"
        
With Columns("A:A").FormatConditions(1).Font
    .Color = -16776961
End With
    
'For red color in A give blank to E until H
[B][COLOR=rgb(184, 49, 47)]If Columns("A:A").Font.Color = -16776961 Then[/COLOR][/B]
[COLOR=rgb(184, 49, 47)][B]
    Else
    
    [/B][/COLOR]
[B][COLOR=rgb(184, 49, 47)]    End If[/COLOR][/B]
    

'Delete Condition

Columns("A:A").FormatConditions.Delete
    
    
End Sub

I've attached the excel display as well here.

Thanks a lot for your help.

 

[Nancy123]

Sorry, the code is actually like this, I haven't done for the part "For red color in A give blank to E until H"

Sub SecondOcurrenceFont()
    
'Give red color to Second Ocurrence

Sheet3.Activate

Columns("A:A").FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($A$1:A1,A1)>1"
        
With Columns("A:A").FormatConditions(1).Font
    .Color = -16776961
End With
    
'For red color in A give blank to E until H
If Columns("A:A").Font.Color = -16776961 Then

    Else
    
    
    End If
    

'Delete Condition

Columns("A:A").FormatConditions.Delete
    
    
End Sub

 

[Fluff]

How about

Sub SecondOcurrenceFont()
    
'Give red color to Second Ocurrence

Sheet3.Activate
With Range("A1", Range("A" & Rows.Count).End(xlUp))
   .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($A$1:A1,A1)>1"
        
   .FormatConditions(1).Font.Color = -16776961
   .AutoFilter 1, RGB(255, 0, 0), xlFilterFontColor
   .Parent.AutoFilter.Range.Offset(1, 4).Resize(, 4).Value = ""
   .FormatConditions.Delete
End With
End Sub

 

[Nancy123]

How about

Sub SecondOcurrenceFont()
   
'Give red color to Second Ocurrence

Sheet3.Activate
With Range("A1", Range("A" & Rows.Count).End(xlUp))
   .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($A$1:A1,A1)>1"
       
   .FormatConditions(1).Font.Color = -16776961
   .AutoFilter 1, RGB(255, 0, 0), xlFilterFontColor
   .Parent.AutoFilter.Range.Offset(1, 4).Resize(, 4).Value = ""
   .FormatConditions.Delete
End With
End Sub

Hi @Fluff,

Thanks for your help, it works. However, it also filtered the blank cells in column A, can we get a formula to just filter the red color cells without any blank cells ?

And also how can I clear / unfilter the data (Show all data back) ?

Thank you.

 

[Fluff]

Does col A have formulae that return ""

 

[Nancy123]

Does col A have formulae that return ""

No

 

[Fluff]

As the countif does not affect empty cells, your blanks are not actually empty. Where did the data come from?