Conditional Cell Change Value

  • Cell A1 can have any value.

    Cell B1 has data validation in place, and thus can only have four values.

    If I type "run" in cell A1, I want the value in cell B1 to change from "Slow" to "Fast."

    The kicker is this: Cell B1 cannot be a formula like =IF(A1="run","fast","slow"). It needs to be the text string "fast" or "slow" or any of the other values in its data validation table.

    I've seen a spreadsheet that populated values based on another cell's entry without a formula, but the spreadsheet has a password on it and I cannot figure out how it was done (see attachment).

    I'm curious if this can be done with a macro... I've successfully written a macro that will change the value in cell B1, but is there a way to cause a value to run only if the value in cell A1 is changed to a specific text value?

    Thanks much! :)

  • Your attachment does not seem to match your problem description. But from your description, it sounds like you might be better off using the CHOOSE() function than data validation. Suggest you take a look at it in Excel HELP.

  • Hi Jas -

    The worksheet change event macro may be what you need. Without knowing more about you applicaiton, its hard to expand much, but the attached shows the macro in its basic form - a change to A1 causes a change to B1. - - Its not in a "module", but in the code for sheet1 (right click on the sheet1 tab, then click on view code to see it)

    Hope this gets you started


  • That's beautiful! Thanks much - it's exactly what I was looking for. I've not yet implemented it on my worksheet (it's at work and I'm at home), but this looks perfect.

    And my local "gurus" said it couldn't be done! :spin:

  • FWIW, you can achieve a similar result using data validation. search the site for 'dynamic lists' or something similar - as I recal, Dave has an example workbook describing a technique...

  • I have a new question for you. Is there any way, in your example, to add to the macro that changing cell A1 to a value OTHER than "run" will cause another action to take place (for example, return a default text value or clear the cell). I'm not sure how to trigger that... perhaps an "else" type line?

    I'm obviously not the best VBA guy, but I'm learning! :wink2:

  • Nevermind... got it... Here's what I'm using:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim myrng As Range
    Set myrng = Range("s22")

    If Intersect(Target, myrng) Is Nothing Then Exit Sub

    If Target.Cells.Value = "Presented Galleria" Then

    Range("O27").Value = " "

    End If

    If Target.Cells.Value <> "Presented Galleria" Then

    Range("O27").Value = "Additional Feature"

    End If

    End Sub

  • Hi Jas,

    You can add a little more scope, and remove tedious multiple Ifs, by using a Select Case statement.

    Modifying the example code slightly:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!