vba any way to make macro smaller

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
536
Office Version
2010
Platform
Windows
Hi.
I did this macro, I would like to know if there are a way to write this with less lines

VBA Code:
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,996
Office Version
2019
Platform
Windows
Like this
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
How about
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,922
Office Version
2019
Platform
Windows
and just for fun

VBA Code:
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

Well-known Member
Joined
Jan 26, 2011
Messages
536
Office Version
2010
Platform
Windows
Like this
VBA Code:
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

Well-known Member
Joined
Jan 26, 2011
Messages
536
Office Version
2010
Platform
Windows
How about
VBA Code:
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

Well-known Member
Joined
Jan 26, 2011
Messages
536
Office Version
2010
Platform
Windows
Thanks DMT, nice molecule. 32
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback.
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
536
Office Version
2010
Platform
Windows
thanks to your ideas I did this and work. Team is always better.
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
A word of warning, if you already have a CF rules for T2:T37 when you run that code, it will not work properly.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,588
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top