Input "YES/NO" to a cell depending other column color-CodePudding

I have set from conditional formatting in vba to make cell color orange if runner weight <6. Now, I am stuck at Step (2) below. Where I'd like to input NO into column Propose? YES/NO if the cell color is orange. The rest will be input as YES. I did try find the similar questions but unfortunately still stuck.

Sub sort()
    Dim R1 As Range
    Dim Condition1 As FormatCondition

    '(1) Change cell to orange if runner weight <6g
    
    Set R1 = Range("G18", "G206")
    R1.FormatConditions.Delete
    
    Set Condition1 = R1.FormatConditions.Add(xlCellValue, xlLessEqual, "=6")
    
    With Condition1
        .Interior.Color = RGB(255, 165, 0)
    End With

    '(2) Input N to <6g runner weight as not collect runner weight, rest input Y

    Range("K18", "K206").ClearContents

CodePudding user response：

You set the color based on another cell's value. Based on that SAME cell you can determine if the next cell should be No or Yes. You don't need to evaluate the color, you need to evaluate the runner weight.

If Range("G18") < 6 then 
   Range("K18").value = "No"
else
   Range("K18").value = "Yes"
end if

You can put that in a loop for all cells in the range, like this

For Each cel In Range("G18:G208")
    If cel.Value < 6 Then
       Cells(cel.Row, "K").Value = "No"
    Else
       Cells(cel.Row, "K").Value = "Yes"
    End If
Next cel

CodePudding user response：

Not sure if conditional formatting can solve this. But you can add the below code in the Worksheet_SelectionChange event and solve it. Change the range and messages accordingly.

CODE:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rng As Range
    Set rng = Range("B2", "B5") 'Change the range address
    Dim ocell As Range
    For Each ocell In rng
        If ocell.Offset(0, -1).value <= 6 Then  'Set the offset as per your req
            ocell.value = "YES"
        Else
            ocell.value = "NO"    'Add what message you want to give
        End If
    Next

End Sub

PIC: