+ Reply to Thread
Results 1 to 9 of 9

Change a cell based on a drop down list

  1. #1
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Change a cell based on a drop down list

    test.xlsm

    I have a drop down list (data validation) for every cell in my range I18:I190 with these 8 options

    Quoted
    Bound
    NTU
    WIP
    Modelling
    Declined
    Non-Renewed
    Extended

    I want the range J18:J190 to be populated with text based on these statements:

    If I18="Bound" then J18="Green",
    If I18="NTU","Declined" or "Non Renewed" then J18="Red", otherwise do nothing.


    I want the macro to be run whenever there is a change to the I18:I190 range, and this should work for all worksheets in the workbook if possible.

    thanks!
    Last edited by nickmax1; 08-23-2012 at 11:23 AM.

  2. #2
    Registered User
    Join Date
    08-23-2012
    Location
    ithaca
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Change a cell based on a drop down list

    in J18
    =if(I18="Bound","Green",If(I18="NTU","red",If(I18="Declined","red",If(I18="Non Renewed","red"))))

  3. #3
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Change a cell based on a drop down list

    Thanks but i need the macro to do the work , because J is a drop down list as well (Red, Amber and Green) which the user needs to decide upon, but the variations above that i need the macro to do the work for are a given (ie static) and wont need any user interaction.
    Last edited by Cutter; 08-23-2012 at 02:22 PM. Reason: Removed duplicate post

  4. #4
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Change a cell based on a drop down list

    Here is what i have so far
    Please Login or Register  to view this content.
    I want to this macro to run if there is a worksheet change to colomn K (K18:190 specifically) and i want the macro to only have one instance in the cell....at the moment if you rerun the macro over and over cell in colomn L will be RedRedRedRedRedRed etc etc

  5. #5
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Change a cell based on a drop down list

    anyone help please?

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Change a cell based on a drop down list

    Hi, nickmax1,

    right click on the tab, view code, code goes there:

    Please Login or Register  to view this content.
    Please test on a copy of the workbook.

    Ciao,
    Holger

  7. #7
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Change a cell based on a drop down list

    Holger
    thanks its so close!! It works but if I change a cell to NTU it changes it to Red as it should, but if I change my cell from NTU to something else for which I expect the macro to do nothing the Red remains....

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Change a cell based on a drop down list

    Hi, nickmax1,

    what do you want it show: nothing?

    Please Login or Register  to view this content.
    Ciao,
    Holger

  9. #9
    Valued Forum Contributor
    Join Date
    08-10-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    721

    Re: Change a cell based on a drop down list

    Holger thank you so much - resolved as such:

    Please Login or Register  to view this content.
    I got round the problem by assigning a colour to any possible configuration.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1