In this problem, there are 3 columns of data. Column A has "Batch ID's" (an example: N9-363B, where, to the left of the hyphen in the ID, the letter is the "Identifier", and the number is the "Key"); Column B has the "Production Date"; and Column C has the "Ship Date". In Excel VBA, how do I create a subroutine that allows the user to select a "key" from its own drop-down and an "Identifier" from its own drop-down?, and when the combination matches any of the "Batch ID's" in Column A, it highlights the entire row GREEN (color index 4)? The drop down menus are pre-coded by the instructor (cell F2 has the "Identifier" and cell F3 has the "Key"), as well as a button called "Run", so that the user can make the selection and hit the Run button. The requirements for this assignment include that when a match to the selection is found, the whole ROW is highlighted; GREEN refers to color index 4; and that when additional rows are added, the sub should automatically detect the size of data (number of rows) and adjust accordingly. Recommendations by the instructor are to use the Range approach to working with data arrays such as this, as opposed to the selection approach.

Below is the code we are started off with:

Below is what an example of the end result should look like in Excel, without the code:

Pption Explicit ' NOTE: For highlighting, use. ColorIndex =4 " For example, Range("Al"). Interior. Colorindex = 4 would color cell Al green Sub HighlightRows() 'Place your code here End Sub Sub Example() " This is just to show how the Identifier and Key functions below can be utilized in VBA code Dim ID As String ID="Y4824X" MsgBox "The identifier is " \& Identifier(ID) \& " and the key is " \& Key (ID) End Sub Function Identifier(ID As String) As String Identifier = Left (ID, 1 ) End Function Function Key (ID As String) As Integer Key = Left (Mid (ID, 4,4),1) End Function Sub Reset() " Obtained through a macro recording: With Cells.Interior - Pattern = xlNone . TintandShade =0 - PatternTintAnd.Shade =0 End With *Important: Before running the GRADER file, make sure to reset the data here to be the exact data on the Original Data tab! (Copy/paste if you need to) Pption Explicit ' NOTE: For highlighting, use. ColorIndex =4 " For example, Range("Al"). Interior. Colorindex = 4 would color cell Al green Sub HighlightRows() 'Place your code here End Sub Sub Example() " This is just to show how the Identifier and Key functions below can be utilized in VBA code Dim ID As String ID="Y4824X" MsgBox "The identifier is " \& Identifier(ID) \& " and the key is " \& Key (ID) End Sub Function Identifier(ID As String) As String Identifier = Left (ID, 1 ) End Function Function Key (ID As String) As Integer Key = Left (Mid (ID, 4,4),1) End Function Sub Reset() " Obtained through a macro recording: With Cells.Interior - Pattern = xlNone . TintandShade =0 - PatternTintAnd.Shade =0 End With *Important: Before running the GRADER file, make sure to reset the data here to be the exact data on the Original Data tab! (Copy/paste if you need to)