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

Question

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

score 3 · Accepted Answer · answered Dec 13 '21 at 04:15

3

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

answered Dec 13 '21 at 04:15

teylyn

34,374
4
53
73

If that resolved your issue, please mark the answer as described in the [tour]. If it didn't, please leave a comment, so I can follow up. – teylyn Dec 13 '21 at 04:15
Nice! This is a classic example of [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) :D – Siddharth Rout Dec 13 '21 at 05:49
thank you @teylyn it worked ~! . I'm too focused to make the orange cell as reference. Where actually I can just simply use the value itself. hehe – zaidi ila Dec 13 '21 at 06:42

score 0 · Answer 2 · answered Dec 13 '21 at 04:21

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:

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

2 Answers2

Linked