Repeat formatting every row

[Atvolpini]

Hi,

I am trying to copy conditional formatting across each row of a worksheet. So far all I have been able to come up with is recording me manually transferring the formatting row by row. The code ends up looking like this, repeated for every row used:

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
Selection.Copy
Rows("3:3").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Rows("4:4").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Rows("5:5").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy

That continues for ~125 rows. Im sure there is a way to do this more efficiently, and would love to know how. Itd be great to apply the desired formatting across each row until an empty row is reached.

Thank you in advance!

 

[Fluff]

How about

Dim rw As Long

For rw = 2 To Range("A" & Rows.Count).End(xlUp).Row
   With Rows(rw)
      .FormatConditions.AddUniqueValues
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      .FormatConditions(1).DupeUnique = xlDuplicate
      With .FormatConditions(1).Font
         .Color = -16383844
         .TintAndShade = 0
      End With
      With .FormatConditions(1).Interior
         .PatternColorIndex = xlAutomatic
         .Color = 13551615
         .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
   End With
Next rw

 

[Atvolpini]

Your code seems to be working perfectly! Thank you!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Atvolpini]

Would it be possible to limit the number of columns the formatting is applied to, in order to limit how heavy the data is to manipulate?

 

[Fluff]

Which columns do you want to limit it too?

 

[Atvolpini]

Columns A through Q

 

[Fluff]

Maybe

   With Range("A" & rw).Resize(, 16)

 

[Atvolpini]

That worked perfectly. Thank You!

 

[Fluff]

Glad to help & thanks for the feedback