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

Assignment 4 - STARTER.xlsm

Problem Statement:

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".


Could someone help me out with the problem above and screenshot your code in your VBEditor?

Did you have an excel question?

What have you tried?

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

You missed nesting your if statement

Please Login or Register  to view this content.

Probably start your loop at row 2

Please Login or Register  to view this content.

The you don't have to add 1 to the color row

Please Login or Register  to view this content.

I edited the Key function, you are looking for the 4th character.

Please Login or Register  to view this content.

Attachment 827763

Sub HighlightRows()
'Place your code here
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

Is this correct?

Did it work?

You need to tell it what ID is.

Please Login or Register  to view this content.

Thank you

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread