New VBA Learner needs help!

[Natrada]

Hi everyone! I'm a new VBA learner. I enrolled a VBA course in Coursera.com and am now at week 4. There is an assignment that I get stuck and do not know how to solve it. I wrote the whole thing in the visual basic already. Please see the attachment. The assignment is in the tab "Problem Statement" Could you please suggest? Assignment 4 - STARTER.xlsm

[6StringJazzer]

Welcome to the Forum Natrada!

Please update your title to summarize your question. Also please include a brief summary of your question in your post so we can determine from your title and post whether we can help, without having to download and open a file attachment. The file should serve to add detail, examples, and data.

[Natrada]

Hi Jeff, sorry for that.


Problem of statement
A set of data has 3 columns of:
- Column A contains the Batch ID
- Column B contains the production date
- Column C contains the ship date

The Batch ID has a two-digit code to the left of the hyphen and a 3- or 4-digit code to the right of the hyphen. The first letter of the Batch ID is known as the Identifier and the leading number of the 3- or 4-digit code to the right of the hyphen is known as the Key. For example, in the Batch ID "N9-363B", the Identifier is "N" and the Key is 3.

Your goal is to create a subroutine that allows the user to select the Identifier from a drop-down menu in cell F2 and the Key from a drop-down menu in cell F3 (these drop-down/data validation menus are already available in the starter file) and any rows of the data (columns A, B, and C) whose Batch ID meets those criteria will be highlighted GREEN.
For example, if we start with the worksheet layout above and run the subroutine with the Identifier set to "C" and the Key set to 2, we would get the data as shown in the screenshot Screenshot 2.png

The RUN button is linked to the HighlightRows subroutine in the starter file. The RESET button is already linked to the Reset subroutine in the starter file so please do not modify it. When run, the RESET button (or Reset sub) will remove any fill color/highlighting from anywhere on the "Data" sheet. However, it will not remove any additional rows that you may add, so be sure to reset the data (by copying/pasting) present in the "Original Data" tab if you add or change any of the original data.

In the Visual Basic editor in the original file also includes :

Please Login or Register  to view this content.

Could you please suggest? Thanks a lot!

[nankw83]

Hi Natrada,

Since your dataset is small, you can loop through the cells & check the condition using simple excel functions which are LEFT() & MID() as shown below

Please Login or Register  to view this content.

[Natrada]

Hi nankw83, thank you very much for your suggestion. Since both functions (=Left and =Mid) were given in the starter file, would it be fine to keep it as it is? Could you please kindly help check what else is missing? Thank you so much!

Please Login or Register  to view this content.

[6StringJazzer]

Natrada:

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however we recommend that you include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

I have added them this time because you are a new member.

[Natrada]

Thank you so much for the suggestion. I will do it next time. This time I have got it resolved last night. Since I'm learning VBA, I guess that I will have more questions or need helps from you guys. Cheers!