VBA that applies a custom number format depending on which drop down choice you make

[bdouglas1011]

I want to apply a custom number format to specific range once a choice is made on a drop down. in (D11) you have a choice of (XXT or NOVA) if a user selects (XXT) I would like the range (D12:D20) to have a custom format "\DI\M-0000" and if they choose (NOVA) "\NV-0000" in that range users only have to enter a 4 digit number and then the custom format adds the "DIM" or "DV" to the number so it reads "DIM-1234" when they enter "1234" or "DV-1234"

How would I do this in VBA and not in the formatting of cells like I have done in the past.

[jindon]

To a worksheet code module

Please Login or Register  to view this content.

?

[bdouglas1011]

If I want to add to this say the same thing just for another column how to continue on

Please Login or Register  to view this content.

how to combine the 2

Thanks I will have to add as I go along

[jindon]

Something like

Please Login or Register  to view this content.

[bdouglas1011]

Not sure if you noticed that column B had different formats.....but ill get back to that ....here is what I am thinking.

I have a Range(A12:K20) depending on which style they choose on the drop down I would like it to only format a portion of the range with the new format. I would like a input box or question be asked for the user to select the range they need formatted. it will change depending on the number of different tools they have. Say they choose that they need Range (A16:k:20) formatted.... this would be an example of the format breakdown:
Formats:
Column B - "\NV-0000"
Column D - "\EX-0000"
Column F - "\DX-0000"
Column H - "\GX-0000"

SO starting on Row 16 it would apply the format in those columns down to row 20 but not to the rows above 16.

[jindon]

Please Login or Register  to view this content.

[bdouglas1011]

It is working but I do see a snag...some of the new serial numbers we enter are not based like the ones we usually use...Ours are always 4 #'s....
some of these are different Ex: (IGH04-11) or EX: (B25)

how can I adjust the code to accept any thing entered and adjust it properly with the prefix I want.

[jindon]

I don't understand what you are talking about.

Upload a workbook clearly showing the problem.

[bdouglas1011]

Right now we enter in "1234" it comes back with NV-1234.
But what if a value entered was "JHK12547885" then the format kicks it back saying value not valid. I just noticed that these new values are not based on a 4 digit number... it varies.

[jindon]

Then

Please Login or Register  to view this content.

[bdouglas1011]

Now it says it is invalid. and clears it out. When the user enters a different value other than a 4-digit number it could be "1234" or say "JHK12547885" the return should be "NV-JHK12547885" or they enter "B25" then the return would be "NV-B25". Sorry if I misled you.

[jindon]

Please Login or Register  to view this content.

[bdouglas1011]

OK I have another request how do I set the data validation on the newly selected range to nothing that way i get no error alerts.

[jindon]

You can ask one question per thread, so you will need to open a new thread for new question.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.