Duplicate a conditional macro to the next row xxx

Question

I'd like to find the way to duplicate this at the following rows. It has to go from C1:E1 than C2:E2 and so on.

Sub Conditional()
Conditional Macro
Range("C1:E1").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlUnique
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

I am very new to excel Thanks in advance

Sgdva · Answer 1 · 2022-02-15T21:29:37.223

0

Recommendations
Selects should be replaced; not addressing for color handling in the solution, but you may consider to change to a RGB logic since themes may vary per user and the color may not be the one that you originally intended to. I will think that you need it in 2 different rulings as now (the duplicates in each column and not in the range).

Solution

Sub Exec_Conditional()
    Call Conditional(Range("C1:E1"))
    Call Conditional(Range("C2:E2"))
End Sub
Sub Conditional(RangeToPerform As Range)
    'if you need to have this condition only in the range, otherwise comment the delete line
    RangeToPerform.FormatConditions.Delete 
    RangeToPerform.FormatConditions.AddUniqueValues
    RangeToPerform.FormatConditions(RangeToPerform.FormatConditions.Count).SetFirstPriority: RangeToPerform.FormatConditions(1).DupeUnique = xlUnique
    With RangeToPerform.FormatConditions(1).Font
    .Color = -16383844: .TintAndShade = 0
    End With
    With RangeToPerform.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic: .Color = 13551615: .TintAndShade = 0
    End With
    RangeToPerform.FormatConditions(1).StopIfTrue = False
End Sub

edited Feb 15 '22 at 21:29

answered Feb 15 '22 at 20:43

Sgdva

2,800
3
17
28

You're missing the `.`'s in your With blocks (I know, copied from OP, but still...). Also use of `Call` antiquated and never needed – chris neilsen Feb 15 '22 at 20:49
My bad on that one thanks for spotting, I fixed it. OT: I use call just for my own standardization purposes to differentiate routines from functions, I haven't found documentation to prove that it's memory wasteful or related, the closest definition for usage that I have found somewhat explicative is at [this article](https://bettersolutions.com/vba/macros/subroutines-call-keyword.htm) – Sgdva Feb 15 '22 at 21:35
1

Re `Call` - even that article acknowledges Call is regarded as obsolete (but then goes on to claim its use makes code more readable, one i dissagree with). [Here's an opposing point of view](https://rubberduckvba.com/Inspections/Details/ObsoleteCallStatement) [and another here](https://stackoverflow.com/a/56874163/445425) – chris neilsen Feb 15 '22 at 21:42
Thank you sooooo much for your help, it is working great! Is there any way that it's working as a loop? instead of adding a line [call] for each row? – Romain Cantalouve Feb 15 '22 at 22:40
There's a way yes, but you need to research about it to fit it to your [specific needs](https://stackoverflow.com/questions/39929553/how-to-loop-ranges-in-vba). – Sgdva Feb 16 '22 at 15:12

Duplicate a conditional macro to the next row xxx

1 Answers1