VBA loops

[Robinkio]

Hi everyone

I have been teaching myself VBA and have now started coding bigger "Projects". However, I am pretty unfamiliar with code Loops, which is why my codes are pretty insufficient and Long. I have tried to read and learn how to shorten my specific codes but could unfortunately not find anything. I would therefore be very happy and appreciate it, if someone of the pros could give me a hint on how to shorten the below code.

Thanks a lot in advance and a great day to the Excel community.

----

  'Form 1a
    If Worksheets("Pivot_Planogramm").Range("BE6").Formula >= .Range("D31") And Worksheets("Pivot_Planogramm").Range("BE6") <= .Range("E31") And Worksheets("Code").Range("C4") = 1 Then
    .Shapes("Form 1a").Fill.ForeColor.RGB = Range("F31").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BE6") >= .Range("D32") And Worksheets("Pivot_Planogramm").Range("BE6") <= .Range("E32") And Worksheets("Code").Range("C4") = 1 Then
    .Shapes("Form 1a").Fill.ForeColor.RGB = Range("F32").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BE6") >= .Range("D33") And Worksheets("Pivot_Planogramm").Range("BE6") <= .Range("E33") And Worksheets("Code").Range("C4") = 1 Then
    .Shapes("Form 1a").Fill.ForeColor.RGB = Range("F33").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BE6") >= .Range("D34") And Worksheets("Pivot_Planogramm").Range("BE6") <= .Range("E34") And Worksheets("Code").Range("C4") = 1 Then
    .Shapes("Form 1a").Fill.ForeColor.RGB = Range("F34").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BE6") >= .Range("D35") And Worksheets("Pivot_Planogramm").Range("BE6") <= .Range("E35") And Worksheets("Code").Range("C4") = 1 Then
    .Shapes("Form 1a").Fill.ForeColor.RGB = Range("F35").Interior.Color
    Else
    .Shapes("Form 1a").Fill.ForeColor.RGB = vbWhite
    End If

    'Form 1b
    If Worksheets("Pivot_Planogramm").Range("BF6").Formula >= .Range("D31") And Worksheets("Pivot_Planogramm").Range("BF6") <= .Range("E31") Then
    .Shapes("Form 1b").Fill.ForeColor.RGB = Range("F31").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BF6") >= .Range("D32") And Worksheets("Pivot_Planogramm").Range("BF6") <= .Range("E32") Then
    .Shapes("Form 1b").Fill.ForeColor.RGB = Range("F32").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BF6") >= .Range("D33") And Worksheets("Pivot_Planogramm").Range("BF6") <= .Range("E33") Then
    .Shapes("Form 1b").Fill.ForeColor.RGB = Range("F33").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BF6") >= .Range("D34") And Worksheets("Pivot_Planogramm").Range("BF6") <= .Range("E34") Then
    .Shapes("Form 1b").Fill.ForeColor.RGB = Range("F34").Interior.Color
    ElseIf Worksheets("Pivot_Planogramm").Range("BF6") >= .Range("D35") And Worksheets("Pivot_Planogramm").Range("BF6") <= .Range("E35") Then
    .Shapes("Form 1b").Fill.ForeColor.RGB = Range("F35").Interior.Color
    Else
    .Shapes("Form 1b").Fill.ForeColor.RGB = vbWhite
    End If

 

[CalcSux78]

There's a bunch of helpful info here for noobs & novices. (I've learned a few new tricks there too)

First off, simplify your code by declaring a few variables. It will make things easier to follow and less code heavy. For your scenario, I'd use a For Each loop.

I couldn't tell if your code segment was inside a with statement so you will need to alter this to fit your case.

Dim sh As Worksheet
Dim rPivot As Range
Dim cel As Range


Set rPivot = Worksheets("Pivot_Planogramm").Range("BE6")
Set sh = ActiveSheet


With sh.Shapes("Form 1a").Fill.ForeColor
    .RGB = vbWhite
    '   may need to fix range.  Couldn't idenify from post
    For Each cel In sh.Range("D31:D35")
        If rPivot >= cel.Value And rPivot >= cel.Offset(1).Value Then
            .RGB = cel.Offset(2).Interior.Color
        End If
    Next cel
End With

 

[Robinkio]

Hi and thanks for your Feedback. I tried your code but I couldn't figure out how to adjust it. Nevertheless, I also tried it with the subsequent code. This works but leaves me with the Problem that it doesn't Color the shapes in the desired Color code. I have 5 different ranges and for each range an individual Color code. Ranges go from "F31-F35" and when I set the range as Range("F"&i) it always takes the Color code in cell F35, even though the value is not in this range. I hope my Explanation is understandable. Would you know how to solve this? Thanks a lot.

'Form 1a
    For i = 31 To 35
    If Worksheets("Pivot_Planogramm").Range("BE6").Formula >= .Range("D" & i) And Worksheets("Pivot_Planogramm").Range("BE6") <= .Range("E" & i) And Worksheets("Code").Range("C4") = 1 Then
    .Shapes("Form 1a").Fill.ForeColor.RGB = Range("F" & i).Interior.Color
    Else
    .Shapes("Form 1a").Fill.ForeColor.RGB = vbWhite
    End If
    Next i

 

[RoryA]

Cross-posted here: http://www.excelforum.com/excel-programming-vba-macros/1166832-vba-loops.html

Please read the forum rules on cross-posting and abide by them in future. Thanks.