Return text in cell based on value entered in another cell

Question

I have columns in my table (F -> I) with potentially unlimited rows, which are drop downs with a simple Yes/No list.

It starts as an empty row and the user inputs data in other rows and then selects either Yes/No based on the questions.

What I'm looking for is some VBA to say If the user has selected 'No' in Column F, then in Column K, prepopulate with "Column F: ". The idea is that anything that is selected as "No", is populated in K so the user can add their notes and reference Column F. For example: "Column F: This did not meet requirements because xxxxx"

I have tried a few examples whilst searching the net but nothing seems to work:

R = 4

'loop to the last row
Do Until Range("F" & R) = ""
    'check each cell if if contains 'apple' then..
    '..place 'Contains Apple' on column B
    If InStr(1, Range("F" & R), "No") Then
        Range("K" & R) = "Test Plan"
    End If
    
    R = R + 1
Loop

I also tried putting that in a worksheet change sub but it didn't work as expected.

Any help appreciated. Thanks

`worksheet change` is a better way to go about it rather than looping through the entire range. Can you share the code for `worksheet change`? Yu may also want to see how to work with [worksheet change](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) — Siddharth Rout, Apr 23 '21 at 10:11
I just copied the above into the worksheet_change thinking it would work, as reading through, it makes sense and should work, but it didn't lol — Martin, Apr 23 '21 at 14:11

score 1 · Answer 1 · answered Apr 23 '21 at 16:08

Is this what you are trying? I have commented the code. For more explanation, I would recommend going through How to handle Worksheet_Change

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range
    
    '~~> Error handling
    On Error GoTo Whoa
    
    '~~> Switch off events
    Application.EnableEvents = False
    
    '~~> Check of the change happened in Col F
    If Not Intersect(Target, Columns(6)) Is Nothing Then
        '~~> Loop through all the cells in col F where
        '~~> the change happened
        For Each aCell In Target.Cells
            '~~> Check if the value is NO
            If UCase(aCell.Value2) = "NO" Then
                '~~> Update Col K
                Range("K" & aCell.Row).Value = "Test Plan"
            Else
                '~~> If not NO then WHAT ACTION? For example user
                '~~> deletes the existing NO
            End If
        Next
    End If
    
Letscontinue:
    '~~> Switch events back on
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

In Action

Glenn G · Answer 2 · 2021-04-23T15:03:25.823

0

Try this code in the Worksheet_Change

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 6 And Target.Value = "No" Then
            Target.Parent.Range("K" & Target.Row).Value = "Column F: "
        End If
End Sub

edited Apr 23 '21 at 15:03

answered Apr 23 '21 at 13:34

Glenn G

667
10
24

Cheers Glenn G, just tried it unfortunately no cigar. I thought it might've been because I had another worksheet change sub in there, but I removed it and it didn't do anything when I selected Yes in Column F – Martin Apr 23 '21 at 14:12
@Martin something I ran into is that "No" and "no" are not seen as the same, so it is case sensitive. See if that is the issue it is having – Glenn G Apr 23 '21 at 15:00
@Martin I've updated my answer with some options (`Option Explicit` and `Option Compare Text`) that go at the top of your module (not inside any procedures) that will change the way that module compares things. This will get around the case sensitivity but you should keep this in mind if you add any other code to the module. – Glenn G Apr 23 '21 at 15:07
Just re-read your comment. The code that I posted only runs if the answer is no. If the answer is Yes then it doesn't do anything. Is that not the intended action? – Glenn G Apr 23 '21 at 15:10
I already had the Options as part of another routine so the case sensitivity wouldn't have been an issue with your code as the defaults are Yes and No anyway, but unfortunately Column K isn't getting updated. So I have a question which asks whether something has been checked. If Yes, then no need to put anything in Column K as all is good, however, if No, then Column K should get pre-populated with something to refer to that column/question, along with conditional formatting which turns the row into another colour (but I've done that bit). – Martin Apr 23 '21 at 15:17
Glenn, `Target.Parent.Range("K" & Target.Row).Value` can be written as `Range("K" & Target.Row).Value` Also your code doesn't handle lot of things. Please do spend sometime reading the link that I gave below the question :) – Siddharth Rout Apr 23 '21 at 15:56
@SiddharthRout thanks for the info on the `Worksheet_Change` event, I rarely do anything with those events and am not very familiar with all the intricacies involved in their use. – Glenn G Apr 23 '21 at 17:02

Return text in cell based on value entered in another cell

2 Answers2