For without Next

[KishorKK]

Hi Friends,


Sub basics6()

Dim myrng As Object
Dim x As Integer

Set myrng = ThisWorkbook.Sheets("sheet3").Cells(Rows.Count, "c").End(xlUp).Row

For x = 1 To x - 1


For Each myrng In myrng.Cells

If myrng.Value = "Pass" Then
myrng.Interior.Color = "Vbgreen"
Else
myrng.Interior.Color = "vbred"

End If

Next myrng

End Sub


I'm not getting where it is going wrong. Pls help

 

[NARAYANK991]

Hi ,

Always use indenting. It helps.

Sub basics6()
    Dim myrng As Object
    Dim x As Integer

    Set myrng = ThisWorkbook.Sheets("sheet3").Cells(Rows.Count, "c").End(xlUp).Row

    For x = 1 To x - 1
        For Each myrng In myrng.Cells
            If myrng.Value = "Pass" Then
               myrng.Interior.Color = vbGreen
            Else
               myrng.Interior.Color = vbRed
            End If
        Next myrng
    Next x '-------------------- This is what was missing
End Sub

Narayan

 

[NARAYANK991]

Hi ,

You should never have a statement such as :

For x = 1 To x - 1

Narayan

 

[NARAYANK991]

Hi ,

vbRed and vbGreen are VBA constants ; they are not to be enclosed within quotes.

Narayan

 

[KishorKK]

Next x i have given but it's not taking

 

[KishorKK]

and also only vbred is executing if pass also red color is applying, how to overcome this

 

[Chihiro]

Few things.

1. I'd not use "Object" to declare range. Do explicit declaration and declare as "Range" object.

2. You are trying to assign row# to object...

3. Variable x isn't doing anything in your code. I'd remove it along with outer loop.

4. Likely that "Pass" isn't in the cell. Check if there is preceding or trailing space.

Sub sampleBasic6()
Dim myCell As Range
Dim lRow As Integer
Dim ws As Worksheet

'Set worksheet to be reused in later operations
Set ws = ThisWorkbook.Sheets("Sheet3")

'Check for last used row# for column index 3 (i.e. column C)
lRow = ws.Cells(Rows.Count, 3).End(xlUp).Row

'Loop through each cell in Range and check if value contains "Pass"
'If it does, change to green and if not change to red
For Each myCell In ws.Range("C2:C" & lRow)
    If myCell.Value Like "*Pass*" Then
        myCell.Interior.Color = vbGreen
    Else
        myCell.Interior.Color = vbRed
    End If
Next myCell

End Sub

Edit: Woops, misspelling. "myCell".
Edit2: Also, you can't loop range within range variable I think. Code amended and tested

 

[Monty]

Hello Kishore.

Sub basics6()

Dim myrng As Object
Dim x As Integer

Set myrng = ThisWorkbook.Sheets("sheet3").Cells(Rows.Count, "c").End(xlUp).Row

For x = 1 To x - 1


For Each myrng In myrng.Cells

If myrng.Value = "Pass" Then
myrng.Interior.Color = "Vbgreen"
Else
myrng.Interior.Color = "vbred"

End If

Next myrng
'''''''''''Need to close one more next as you have two for loops.
End Sub

Hope you got it..