I've got one more example I wanted to go through that's going to show you how we can iterate through an array of data, and I'm going to be using the range approach in this example. We've got cookie sales for February and March. We've probably used this dataset for at least one other screencast. We have sellers, buyers, the cookies, the quantities, the cost per box, and the sales. What we're going to be doing here is we're going to be calculating the maximum sales. We're going to be looking up the maximum sales and the minimum, and then we're going to highlight that cell. So let me click "Run", and we have highlighted red the minimum sale and we've highlighted blue the maximum sale of 38.50. Now this adapts to the size. So this is a much larger set of data, a lot more sales, and we can run the same subroutine. It's the exact same subroutine. It's detecting the number of rows here, and we highlight 40 as the maximum and five as the minimum. Finally, there's a reset button here. The reset button, I've already got the code for that in the starter file. We reset that and it's just going to remove any of the highlighting just so you can start fresh. That's what we're going to be creating in this screencast. I've got a starter file here highlighting max and min values in a range.xlsm. We have our February data, our March data, and then if we go over to Visual Basic, I have a highlight max and min sub that we're going to be filling out, and then I've got a reset sub. Now the reset sub, I just recorded a macro for how you can remove any of the formatting in cells. This is just going to remove the formatting, the fill color of all cells on that sheet. The approach here is going to be we want to determine first the min and max of the sales column, so we're going to calculate those. We're going to store those in variables min and max. Then we're going to iterate through and we're going to determine where those are found, and once we find them, we're just going to highlight them. Now you can record a macro for how to highlight cells a different color. So you could go to the Developer tab, Record Macro, and then you could select these colors, but I've already done that for you. We're going to use the color index 3 for red and 33 for blue. You can also look those up on the web, if you'd like. Let's go ahead and start. The first thing we're going to do is we're going to dim a couple of variables. Nr, that's going to be the number of rows, i is going to be index of iteration. We're going to have a four-loop in this code. Min is going to be a double, and max is also going to be a double. Min and max we're going to obtain using the worksheet function min and max, and those are just the min and max sales. The next step, I like to just reset. In case we run this before, we just want to start a fresh. So we can run the reset sub. We'll then count the number of rows. We're going to use the CountA function in Excel to count the number of columns in A, but we have to subtract two because we have the cookie sales title there and we have this column header in cell A3. So we're going to subtract two. Next, we're going to use the worksheet function min that's built into Excel to calculate this min variable. We're calculating the min of our F column. So we're going to do range F4. That's our first value of sales down to F and something. This is going to be written in terms of nr, but then we're going to have to add something. So why don't you guys figure out what we're going to have to add onto that, nr plus what? If you said three, then you're correct. Let's just take a look at this in our example here. The number of rows is 11. We're going to be starting in cell F4, and we're going to be going up to F14. Row 14 here on the spreadsheet is the 11th row, so we have to go to range nr plus 3. That's why we get the three there. Similarly, we're going to look through range F4 through F and nr plus 3 for the max using the max worksheet function that's built into Excel. Now we're going to enter into a four-loop. We're going to iterate through each item in column F and we're going to check to see if it's equal to the min or the max. If it's equal to the min, we're going to highlight it red. If it's equal to the max, we're going to highlight it blue. That's what we're doing here with this two-way if-then. If range F and i plus 3, we're starting i with 1. So we're going to be starting with F4. In the first case here, NR is 11. So we're going to be going all the way up through range F14. So this will work, and this is going to adapt to the size of our worksheet. If I run this on the March tab, we have 16 items here. So it should work the same. If we encounter the min in a particular cell in the F column, we're going to change the Interior.ColorIndex to 3, so that's red. If we find the max, then we're going to make it blue, and that color index is 33. That's it for this subroutine. Let's go ahead and step through it and we can see if it's working. Let's go ahead and press F8. The first thing we're going to do is jump down into the reset sub. That's going to clear any previous shading or filling that we had, and then we're going to count the number of rows, that's 11. We're going to calculate the min of 3.5, the max of 38.5, and then we're going to enter into this four-loop going from 1 to nr of 11, checking each item in column F to see if it's equal to the min and max. The min should be found in the eighth row and the max should be found in the fifth row. Let's go through here, i is 3, i is 4, now i is 5. So you see that we've gone into this. We found the max in the fifth row, we're going to make that blue, and then we're going to resume to i equals 6, i equals 7, now i equals 8. Row 8 is the min, so we're going to convert that interior color to red, and then we keep going and going, and then we end the sub. Real quick, let's just make sure it's working on the March sheet. Because I wrote the code so it adapts to the size of the sheet, I can just run this on a different size dataset and it works perfectly. Then we can reset, if you'd like, and then we can reset on the February tab. One more thing, if I wanted to highlight the entire row, this is a big hint for Homework Assignment 4, then in the code, you probably want to add in here maybe range A and i plus 3, change the color index to 3, and another line for B and i plus 3 and so on, and that will highlight the entire row. So that's a big hint for Assignment 4. Hopefully, you learned a little bit more about how you can highlight rows based upon a certain criteria in this screencast. Thanks for watching.
