First, let me explain "Target", because having a firm grip on that is essential to answering a few of your questions. This routine runs whenever you make a change to a cell on the worksheet (but NOT when a cell value changes due to a formula in the cell). "Target" is the range that was changed; thiscould be a single cell or a large range of cells depending on what the user changed. In the case where what they changed is a selection from a drop-down validation list, this is a single cell.
When you say "any" data validation, do you mean any data validation with the same settings or literally ANY data validation. I know I have to "apply the routine", but I wasn't sure exactly how to apply them to some and not others. The reason I ask is because I have multiple drop-downs all throughout the worksheet, some of which are on the same page, but have different source material. I have quite a few dependent drop-downs that I'm using, based on Named Ranges.
Obviously, we do not want this routine to run for EVERY change that is made on the worksheet. We only want it to change when a user has made a change in one of the drop-down validation selections. In the first example I posted, I had validation lists in cells A1:A6. So, if the "Target" column was not 1 (Excel uses column Numbers internally and only displays letters because that is what most people seem to prefer), then we exited the routine. Similarly, if the Target row was greater than 6, we exited the routine.
In the revised version, I thought the above was too limiting. So, instead, I check the validation of the Target. If it has no validation, or if the validation is not a list (validation type 3), then we exit the routine.
If you want the routine to run for SOME but NOT ALL of your list-validated cells, then you might want some combination of these logic in the first example and the second example.
"If Target.Column <> 1 Then Exit Sub" which I'm assuming means that the DV selection had to be 1
Maybe. It did not mean that they selected the first thing on the list. It meant that the CELL they made the selection in was in Column A (column number 1).
I'm not sure if those cells being merged changes things at all?
The way it is written right now, it probably does. Because your initial explanation was that the dependent cell was in column B. So, right now, the code will format the cell one column to the right of the cell with the DV.
the selection on the row right beneath it is dependent on what is selected above it
So, we need to change the offset from (0,1) to (1,0). Offset(0,1) means "one column to the right in the same row"; Offset(1,0) means "one row down in the same column".
wasn't sure if the Offset part of the code would have to change based on the merged cells
will need to change it for sure. At least because we want to format one row down vs. one column to the right.
does VBA count A:N as one cell or 14?
Both. If you put this code in your workbook (either in a Module or in the same place the Event code is now) and run it, you will see that the first message box tells you that selecting cell A1 selects 14 columns, but offsetting one column from A1 puts you in O1. Weird, huh? But, if you think about it, it makes sense.
I can't believe that you guessed the EXACT colors I was thinking of in your second example!
There just are not that many good colors. I was running out of choices.
your second example seems more complex
There really are only 2 major differences. One is deciding whether to run the routine or not. That was explained above (but, if not explained well enough, holler and I'll give 'er another go). The other is using the MATCH function instead of typing in exact text for all of the possible selections.
Indeed, as you now admit that the lists depend on previous selections, there would be NO WAY to make this code work without using something like MATCH. (And, by the way, I thought about the possibility of using Named Ranges for the List formula. If the Name works for the List, it should work for this routine.)
Since you obviously know how to use VLOOKUP, then you will understand MATCH. It is a close cousin. You can use MATCH in a workbook to find which row contains the MATCH that VLOOKUP finds. The input variables to MATCH are: what you are looking for (text or number), the range you expect to find it, and exact match or not. So, using MATCH with the final input "0" is the same as VLOOKUP with the final input FALSE.
I'll check back to see if there are further questions.
Once again, thanks a lot.
Nada. The reason I lurk here so often is because I never woulda dreamed up some of the stuff people ask about. I learn every day from this forum. It's pretty amazing.
Bookmarks