Insert "YES" when criteria is met

Question

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

Try `Cells(cell.Row, 18).Value = "YES"`. Also, when you ask a question, other than stating what you want to do and including your code attempt, you need to also explain what's wrong with your code (if an error occured, give the error code and description. If it doesn't work as expected, explain what did the code do and what are you expecting instead.) — Raymond Wu, Sep 29 '21 at 12:44
`For Each cell In ActiveCell.Row` is not actual working code. It's rather unclear what you are asking/trying. Do you just want to loop all cells in a single row and check their interior color? — JvdV, Sep 29 '21 at 12:49
Assuming that you want to loop through the cell of your selected cells, try `For each cell in Selection.Cells` — Raymond Wu, Sep 29 '21 at 12:55

Vityata · Accepted Answer · 2021-09-29T14:11:35.463

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 - How to avoid using Select in Excel VBA
In the code above .Cells(1, "S") is used, as it brings more sense to the reader, instead of .Cells(1, 19).

If the idea is to check 2 columns, for example Q and R, whether one of them is vbGreen, then OR in the condition should return the following result:

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.

@TomBrunberg - could be, depending whether one reads the question or takes a look at the picture. Anyway, added a bit more to the answer. — Vityata, Sep 29 '21 at 14:12
Sure, with selective reading anything could be claimed to be correct. But thanks for your edit, although the "NO" is still missing. — Tom Brunberg, Sep 29 '21 at 16:21

Insert "YES" when criteria is met

1 Answers1