Excel VBA For Creative Problem Solving Part 1 Assignment 4: Help Needed

INSTRUCTIONS: In the "Assignment 4 - STARTER.xlsm" file, you will find information for a bunch of batches from a certain process. Column A contains the Batch ID, Column B contains the production date, and 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.

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.

IMPORTANT: Also contained within the starter file are 3 additional procedures: 1) a sub called Example, 2) a function called Identifier, and 3) a function called Key. The Identifier function takes as an argument a Batch ID and outputs the Identifier. The Key function takes as an argument a Batch ID and outputs the Key. I encourage you to utilize these functions within your HighlightRows sub - you can see how to use these two functions in the Example sub.

Requirements:

1) When a match is found in a row, all three columns of data (columns A, B, and C) must be highlighted green.

2) "Green" refers to the interior color index equal to 4. For example, Range("A1").Interior.ColorIndex = 4 would color cell A1 green. The grader file is specifically looking for this ColorIndex! If you color the cells a different shade of green, it won't pass the grader!

3) When additional rows are added to the data (for example, in row 27 and beyond), your sub should automatically detect the size of data (number of rows) and adjust accordingly.

Hints

I would recommend using the Range approach to working with data arrays such as this. See the screencast "Iterating through a Selection vs. a Range" and pay close attention to the approach using Range instead of Selection.

Note that in the starter file, cell F2 has been named "identifier" and cell F3 has been named "key".


I do not understand what is my mistake, can you please help me? Only Sub Highlight Rows() is created by my own, the others are created by Instructor.

Option Explicit

' NOTE: For highlighting, use .ColorIndex = 4
' For example, Range("A1").Interior.ColorIndex = 4 would color cell A1 green
Sub HighlightRows()
Dim nr As Integer, I As Integer, ID As String, key As Integer
nr = WorksheetFunction.CountA(Columns("A:A"))
For I = 2 To nr
ID = Range("A2:A" & nr)
    If identifier(ID) = Range("identifier") And key(ID) = Range("key") Then
    Range("A" & I & ":" & "C" & I).Interior.ColorIndex = 4
    End If
Next I
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 = "Y4-824X"
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
    .PatternTintAndShade = 0
End With
End Sub