Clear content in a dependent cell when new selection is made

[JLHSolutions]

Hello,

I have just started using Excel to track the status of our jobs. I am still learning how to do things using Excel as I came from using FileMaker. What I have right now is a List that gives me choices for the status of the job. This is in column H. In column J, I have a value list that changes depending on what is selected in column H. This part works great.

What I am trying to do is to clear the selection in Column J when the selection changes in column H. From what I can gather on Google, I need to use VB script, but I haven't been able to figure out what to use to write this code. I only want it to change the value in the current row as I am using each row for the a different job. I will have many rows visible at one time as we have lots of jobs that are in various stages.

I am hoping someone here can help me with this. I don't think its too difficult, but as I am new to doing this type of stuff in Excel, I am stumbling around trying to figure it out.

Thanks in advance.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. You will have to modify the macro to suit your needs. The values in red are the values in column H and those in blue are the resulting values in column J. You will have to add additional "Case" statements for each additional value in column H. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "[COLOR="#FF0000"]a[/COLOR]"
            Target.Offset(0, 1) = "[COLOR="#0000FF"]AA[/COLOR]"
            Target = ""
        Case "[COLOR="#FF0000"]b[/COLOR]"
            Target.Offset(0, 1) = "[COLOR="#0000FF"]BB[/COLOR]"
            Target = ""
        Case "[COLOR="#FF0000"]c[/COLOR]"
            Target.Offset(0, 1) = "[COLOR="#0000FF"]CC[/COLOR]"
            Target = ""
    End Select
End Sub

 

[JLHSolutions]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. You will have to modify the macro to suit your needs. The values in red are the values in column H and those in blue are the resulting values in column J. You will have to add additional "Case" statements for each additional value in column H. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "[COLOR=#FF0000]a[/COLOR]"
            Target.Offset(0, 1) = "[COLOR=#0000FF]AA[/COLOR]"
            Target = ""
        Case "[COLOR=#FF0000]b[/COLOR]"
            Target.Offset(0, 1) = "[COLOR=#0000FF]BB[/COLOR]"
            Target = ""
        Case "[COLOR=#FF0000]c[/COLOR]"
            Target.Offset(0, 1) = "[COLOR=#0000FF]CC[/COLOR]"
            Target = ""
    End Select
End Sub

Hi mumps!
Thanks for the reply. So in column H, I have a dropdown list that comes from a table. Lets call this the main categories. Then in column J, I have other lists that come from varies tables. Let's call these subcategories. These subcategories change based on the main category. I am using Data Validation for the list and calling them using the INDIRECT function. Using your script, it looks like I am limited to fixed values.

I am not sure how to modify the script so that it will just erase the subcategory text so that the field will be blank and then I will see that blank and then know I have to enter something in there.

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[JLHSolutions]

https://www.dropbox.com/s/xs5ibfw1z9s8l60/JOB LIST.xlsx?dl=0

In column H, when you change the job status, the text in field is a lookup that shows what the value for the letter is in column H. That is done via VLOOKUP. Then in column J, that is where the subcategories are. That is what I want to clear after I change the text in H. If you look at the ValueList worksheet, you can see the tables that I am pulling the subcategories from. Right now, my lists aren't complete, but its a start for now.

I am not sure how to go about clearing the text from the cell in column J on a particular row when I change the text in that same row on column H.

 

[mumps]

Try this macro in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Target.Offset(0, 2).ClearContents
End Sub

 

[JLHSolutions]

That works perfect!! Thank you!!

Can you explain to me what it does so I can learn how to write this type of stuff myself.

 

[mumps]

You are very welcome.
Since the macro is triggered automatically when a cell in the sheet is changed, you don't want it to run unless you change a cell only in column H. The first line of the code restricts the change to column H. The second line of code clears the contents of the cell 2 columns to the right of the target cell which refers to the cell in column J. I hope this helps.

 

[JLHSolutions]

I am getting a Run-time erro '1004':
Application-defined or object-defined error.

Any ideas what that means?

 

[mumps]

Are you getting the error if you change any cell in column H or just certain cells in column H?