0

Heello, first time here, need some help to avoid repetitive code.

I need to change String and color for conditionnal formatting through loop, need to increment "item2" loop (one time) each time "item1" loop, problem is "item2" will loop x times before "item1" can increment the loop

I hope I made myself understood, I'm a bit stuck, sorry for my low IQ :) Thanks for any answers

        Dim item1 As Variant
        Dim strNames(1 To 3) As String
        strNames(1) = "Name1"
        strNames(2) = "Name2"
        strNames(3) = "Name3"
        Dim item2 As Variant
        Dim strColors(1 To 3) As String
        strColors(1) = 15773696
        strColors(2) = 5296274
        strColors(3) = 5287936
        
        For Each item1 In strNames
        
        Range("A4:N4,A9:N9,A14:N14,A19:N19,A24:N24,A29:N29").Select
        Selection.FormatConditions.Add Type:=xlTextString, String:=item1, _
            TextOperator:=xlContains
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic

        For Each item2 In strColors
        .Color = item2

        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        
        Next item2
        Next item1
mildcnvce01
  • 1
  • 1
  • 3
    `Dim i As Long`, `For i = Lbound(strNames) to UBound(strNames)`, then refer to `strNames(i)` and `strColors(i)`. – BigBen Apr 11 '22 at 19:23
  • 3
    Side note: You generally want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Apr 11 '22 at 19:36
  • 1
    @BigBen Omg, Thanks, it works, I've tried some search but found nothing, and answer was really fast ! You rocks ! Can't upvote or mark as answer, cause I'm below 15 reps points ! @ cybernetic.nomad Thanks, I'll take a look :) – mildcnvce01 Apr 12 '22 at 01:03

0 Answers0