Repeat formatting every row

Atvolpini

New Member
Joined
Oct 26, 2017
Messages
15
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
How about
Code:
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
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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?
 
Upvote 0
Which columns do you want to limit it too?
 
Upvote 0
Maybe
Code:
   With Range("A" & rw).Resize(, 16)
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top