Insert "YES" when criteria is met-CodePudding

I am trying to insert the text "YES" in column(S) and in the same row as the condition is met in:

Sub IF_Color()
    Dim cell As Range
    For Each cell In ActiveCell.Row
        If cell.Interior.Color = vbGreen Then
            ActiveCell.Column(18).Value = "YES"
        End If
    Next cell
End Sub

Example	Example	Condition met (Output Always in this column (S) )
Condition is met		YES

Example	Example	Condition met (Output Always in this column (S) )
Condition is met	Condition is met	YES

Example	Example	Condition met (Output Always in this column (S) )
	Condition is met	YES

Example	Example	Condition met (Output Always in this column (S) )
Condition is not met	Condition is not met	NO

CodePudding user response：

Avoiding ActiveCell and defining the range through you should loop should be as easy as this one:

Sub IfColor()

    Dim myCell As Range
    
    Dim wks As Worksheet
    Set wks = Worksheets("Sheet1")  'write the name of the worksheet
    
    Dim lastRow As Long
    lastRow = 20 'or find a suitable way to define the last row
    
    With wks
        For Each myCell In .Range(.Cells(1, "S"), .Cells(lastRow, "S"))
            If myCell.Interior.Color = vbGreen Then
                myCell = "YES"
            End If
        Next myCell
    End With
    
End Sub

Must read for VBA -
```
If myCell.Offset(columnoffset:=-1).Interior.Color = vbGreen Or myCell.Offset(columnoffset:=-2).Interior.Color = vbGreen Then
```
The idea is that myCell.Offset(columnoffset:=-1).Interior.Color checks column R and columnoffset:=-2 is responsible for Q.