Macros to VBA

ArhamRajput · Mar 19th 2020

Please convert this Highlight Duplicate Macro into VBA.

Code

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("E:E").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Display More

**royUK** · Mar 19th 2020

Macros are VBA. What do you mean?

ArhamRajput · Mar 19th 2020

It is long code to perform the action i want short code as like below.

Code

Set Source = Range("E2:E" & Lastrow)
Source.Interior.Color = RGB(255, 255, 255)
For Each Cell In Source

    If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
        Cell.Interior.Color = RGB(255, 199, 206)
     End If
        
    If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
      Cell.Font.Color = RGB(156, 0, 6)
        
End If

Display More

Justin Doward · Mar 21st 2020

I think this is achieving the same thing:

Code

 Sub ColourMe()
 
 Dim MyRnge As Range
 Dim ws As Worksheet: Set ws = Sheet1
 
 Set MyRnge = ws.Range("E:E")
 
 With MyRnge
 
    .FormatConditions.AddUniqueValues
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).DupeUnique = xlDuplicate
    .FormatConditions(1).Font.Color = -16383844
    .FormatConditions(1).Font.TintAndShade = 0
    .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    .FormatConditions(1).Interior.Color = 13551615
    .FormatConditions(1).Interior.TintAndShade = 0
    .FormatConditions(1).StopIfTrue = False
    
End With

End Sub

Display More

**royUK** · Mar 21st 2020

Why not use Conditional Formatting - more efficient than VBA

ArhamRajput · Mar 23rd 2020

Justin Doward

Thanks for Your help.

ArhamRajput · Mar 23rd 2020

royUK

Want to use duplicate code with other codes to get the result.

Macros to VBA

Participate now!

Share