vba any way to make macro smaller

[montecarlo2012]

Hi.
I did this macro, I would like to know if there are a way to write this with less lines

sub highL_Dup()
Range("R2:R37").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$26:$G$26,R2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 65535
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[jasonb75]

Like this

sub highL_Dup()
With Range("R2:R37")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($B$26:$G$26,R2)"
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 65535
    .FormatConditions(1).StopIfTrue = False
End With
End Sub

You could remove 2 more lines, but it would mean making the rest longer so would be completely pointless.

 

[Fluff]

How about

Sub highL_Dup()
   With Range("R2:R37")
      .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($B$26:$G$26,R2)"
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Interior.Color = 65535
      .FormatConditions(1).StopIfTrue = False
   End With
End Sub

 

[dmt32]

and just for fun

Sub highL_Dup()
    Dim FormatCond As FormatCondition
    Dim rng As Range
    Set rng = Range("R2:R37")
    rng.FormatConditions.Delete
    Set FormatCond = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=COUNTIF($B$26:$G$26,R2)")
    FormatCond.Interior.Color = 65535: FormatCond.StopIfTrue = False: FormatCond.SetFirstPriority
End Sub

Dave

 

[montecarlo2012]

Like this

sub highL_Dup()
With Range("R2:R37")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($B$26:$G$26,R2)"
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 65535
    .FormatConditions(1).StopIfTrue = False
End With
End Sub

You could remove 2 more lines, but it would mean making the rest longer so would be completely pointless.

Thank you Sir.

 

[montecarlo2012]

How about

Sub highL_Dup()
   With Range("R2:R37")
      .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($B$26:$G$26,R2)"
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).Interior.Color = 65535
      .FormatConditions(1).StopIfTrue = False
   End With
End Sub

Thank you Sir, I really appreciate you take time for me.

 

[montecarlo2012]

Thanks DMT, nice molecule. 32

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[montecarlo2012]

thanks to your ideas I did this and work. Team is always better.

sub
Range("T2:T37").FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($B$25:$G$25,T2)"
With Selection.FormatConditions(1).Interior
.Color = 65535
End With
end sub

 

[Fluff]

A word of warning, if you already have a CF rules for T2:T37 when you run that code, it will not work properly.