Loop runs but produce partial result

[Winston618]

Was trying to clean up my clumsy code with the idea below, however, I run into the a new problem. Can someone tell me why it behaves like so? Thanks!

Dim x As Integer, y As Integer, z As Integer 'y and z are newly added

For x = 2 To 500
For y = 1 To 10
z = 6
    If Cells(x, z).Interior.Color = vbYellow Then
        Cells(x, y).Interior.Color = vbGreen
'        Cells(x, 1).Interior.Color = vbGreen
'        Cells(x, 2).Interior.Color = vbGreen
'        Cells(x, 3).Interior.Color = vbGreen
'        Cells(x, 4).Interior.Color = vbGreen
'        Cells(x, 5).Interior.Color = vbGreen
'        Cells(x, 6).Interior.Color = vbGreen
'        Cells(x, 7).Interior.Color = vbGreen
'        Cells(x, 8).Interior.Color = vbGreen
'        Cells(x, 9).Interior.Color = vbGreen
'        Cells(x, 10).Interior.Color = vbGreen
        Else

        End If
        Next y
        Next x

 

[Kenneth Hobson]

It is doing just what you told it. Note what happens when say F2 is yellow, it turns F2 green. Then, your IF is False for column G to J.

What was the goal? If it was to change a row's column A to J interior color to green if it's column F was yellow then:

Sub Main()
  Dim r As Range, c As Range
  Set r = Range("F2:F500")
  For Each c In r
    Select Case c.Interior.Color
      Case vbYellow
        Range(Cells(c.Row, "A"), Cells(c.Row, "J")).Interior.Color = vbGreen
      Case Else
    End Select
  Next c
End Sub

Note that conditional format colors are not addressed in this code.

 

[Winston618]

It is doing just what you told it. Note what happens when say F2 is yellow, it turns F2 green. Then, your IF is False for column G to J.

What was the goal? If it was to change a row's column A to J interior color to green if it's column F was yellow then:

Sub Main()
  Dim r As Range, c As Range
  Set r = Range("F2:F500")
  For Each c In r
    Select Case c.Interior.Color
      Case vbYellow
        Range(Cells(c.Row, "A"), Cells(c.Row, "J")).Interior.Color = vbGreen
      Case Else
    End Select
  Next c
End Sub

Note that conditional format colors are not addressed in this code.

Thank you for pointing out the problem behind my logic, I got it now.

 

[Winston618]

One following up question, if I want to set the range F2:F500 to a dynamic range, how can I do it?

It is doing just what you told it. Note what happens when say F2 is yellow, it turns F2 green. Then, your IF is False for column G to J.

What was the goal? If it was to change a row's column A to J interior color to green if it's column F was yellow then:

Sub Main()
  Dim r As Range, c As Range
  Set r = Range("F2:F500")
  For Each c In r
    Select Case c.Interior.Color
      Case vbYellow
        Range(Cells(c.Row, "A"), Cells(c.Row, "J")).Interior.Color = vbGreen
      Case Else
    End Select
  Next c
End Sub

Note that conditional format colors are not addressed in this code.

 

[Marc L]

A must see in VBA inner help : UsedRange …​

 

[Kenneth Hobson]

These method like the other assumes that the macro is ran with the ActiveSheet being the one to act on.

The first one commented is the one that I typically use. Of course one should know their data. If no value in F2 nor below, F1 is returned. For the 2nd, If no data is used in column F, Nothing is returned.

'Set r = Range("F2", Cells(Rows.Count, "F").End(xlUp))
  'Errors if no intersection is found, check: If r Is Nothing then...
  Set r = Intersect(Columns(6), ActiveSheet.UsedRange)
  Debug.Print r.Address