Setting High and Low Limit for Inventory

Question

So I am running into some trouble with VBA. I have been working on an inventory project, and just as I am about to complete it I was thrown an additional task involving VBA. I am very inexperienced with VBA, so hopefully someone can point me in the right direction.

Unfortunately, I cannot post actual pictures of the inventory sheets, so I will make a mock-up. The actual quantities of the items are in column G, starting at row 10 down to row 2084. 

Example: 2 8 4 13 29 29 38 55 8 75.

What my customer wants is to set a high and low limit of the inventory quantities with a VBA script. The requested parameters are as follows:

**Low Limit:** 
IF value is 10 or less - set low limit to 10;
IF value is between 10 and 30 - set low limit to 10;
IF value is 30 or greater - set low limit to 30;

**High Limit:** 
50% more than what each value is, rounded to the nearest whole number, unless that value is 10 or below. In which case the high limit would be 15, since the low limit of those values is set to 10. 

The inventory quantities that I currently have are the initial quantities. These quantities will be changed as things are removed/added to the inventory. However, these initial quantities are what is going to be used as a baseline for the high/low limits. So my thought is that the initial quantities need to be stored into an array and those initial quantities in the array are what will be used to calculate the high/low limits, even when the inventory is updated. If that is not possible I will just add an initial inventory column to the spreadsheet, but it is a fixed template, so they would prefer that I not do that.

Additionally, after running the VBA script to determine the high/low limits, I would like the individual quantity cells to turn red if that value is under the low limit and blue if the value is above the high limit. Obviously, the first time that it is run, there will be no values that are above the high limit because those values will be used to calculate the high limits. However, all the quantities that are initially below 10 will have their cells turn red because they are starting out below their defined low limit.

I know that this is a lot, but any help would be appreciated as I'm not sure where to start. Thank you!!

Answer 1

I believe this is what you need.

If you want to save highlimit on initial values, you need to create new sheet call it for ex. Config and with using normal excel function create high limit values. (In Sheet1 I create list of your examples values)

=ROUND(IF(Sheet1!A1<=10; 15;Sheet1!A1*1,5);0)

Then you need to copy this cells and paste only values in Config instead formulas.

Here you have code which does what you want.

Private Sub prcCheckLimits()
    Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
    Dim wsHighLimit As Worksheet: Set wsHighLimit = ThisWorkbook.Sheets("Config")
    Dim lngRow As Long 'lastRow
    Dim i As Long

    lngRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 1 To lngRow
        With ws
            If .Cells(i, 1).value < fncLowLimit(.Cells(i, 1).value) Then
                .Cells(i, 1).Interior.ColorIndex = 3
            ElseIf .Cells(i, 1).value > wsHighLimit.Cells(i, 1).value Then
                .Cells(i, 1).Interior.ColorIndex = 37
            Else
                .Cells(i, 1).Interior.ColorIndex = 0
            End If
        End With
    Next i
End Sub

Private Function fncLowLimit(value As Long) As Long
    Dim result As Long

    If value < 30 Then
        result = 10
    Else
        result = 30
    End If

    fncLowLimit = result
End Function