CODEWITHSUNDEEP
vbaexcel
Markus Sacramento
Markus Sacramento

Reputation: 364

Nested For each loop "doesn't work"

I am trying to created a nested For each that needs to match value in Column C & E to continue. It allways changes all values. If it sais "Yes" in Column C but value in Column E isn´t south it still changes the format of "Yes".

Sub LoopRange()
'Step 1:  Declare your variables.
   Dim MyRangeC As Range
   Dim MyRangeE As Range
   'Dim MyRange As Range
   Dim MyCellC As Range
   Dim MyCellE As Range
   'Dim Cell As Range
   'Dim M As Long
    Dim N As Long
'Step 2:  Define the target Range.
   'M = Cells(1, 1).End(xlDown).Row
    N = Cells(1, 1).End(xlDown).Row
    Set MyRangeC = Range("C2:C" & N)
    Set MyRangeE = Range("E2:E" & N)
'Step 3:  Start looping through the range.
    For Each MyCellE In MyRangeE
     For Each MyCellC In MyRangeC
'Step 4:  Do something with each cell.
    If MyCellE.Value = "South" And MyCellC.Value = "Yes" Then

        MyCellE.Font.Bold = True
        MyCellC.Font.Italic = True

    End If


    'If MyCellE.Value = "North" And MyCellC.Value = "Yes" Then
        'MyCellC.Font.Bold = True
        'MyCellE.Font.Italic = True
    'End If
'Step 5: Get the next cell in the range
    Next MyCellC
    Next MyCellE
    'Next Cell
End Sub

Below is the working code:

Sub LoopRange()

Dim N As Long
   N = Cells(1, 1).End(xlDown).Row

   For i = 2 To N
     If Range("E" & i).Value = "South" And Range("C" & i).Value = "Yes" Then
       Range("E" & i).Font.Bold = True
       Range("C" & i).Font.Italic = True
     End If
   Next i

End Sub

Upvotes: 2

Views: 102

Answers (1)

Egan Wolf
Egan Wolf

Reputation: 3573

You don't run your data row by row. Instead, it runs all cells in C for every cell in E. That means, you need only one "South" in E to change all "Yes" in C.

To solve it, you can change your loop to index based:

For i = 2 To N
    If Range("E" & i).Value = "South" And Range("C" & i).Value = "Yes" Then
        Range("E" & i).Font.Bold = True
        Range("C" & i).Font.Italic = True
    End If
Next i

Upvotes: 3

Related Questions