Greetings all and thanks for taking the time to look at my "little" problem.

So, overall, I am not the most experienced person at VBA, and especially when it comes to Excel, am quite rusty. Thus I find myself in a situation where I need to do something that I most certainly know is possible, but can't seem to make it happen. Well, I might have made it happen in one of my more interesting subs that caused Excel to implode... but we will never really know if it "worked".

I would appreciate if beyond just throwing out "the right code", you could also help by showing your thought process or why it should/could/will work. Ultimately while I need a working answer for this one situation, I would also like to come away alittle smarter for the future.

So now we are at the fun part. What am I trying to do?

Well for starters I am using Excel 2007 on both XP and 8.1.

Essentially I have a Cell that needs to compare itself to another Cell earlier in the spreadsheet from it when a value is entered. If that cell is greater than or equal to, or less than, I need it to write/change the Cell below it. However if the earlier cell is empty, I need it to check against an even earlier cell, and so-on until it reaches a particular cell. If THAT cell is empty then it compares itself to a fixed cell.

The values won't be the same, so I can't expect any (including the fixed cell) to have a definite value. The entered value could be anything from 1 to 999; though it WILL always be numbers.

Some tricks are that the "range" (not really a range, I don't think) start at Column F and Row 30 and go to Column DA and Row 246. The cells being compared are only going to be in one column (so something in F will only every be compared to F), and they will always be 8 apart - except the fixed cell.

So essentially I need the value in 246, F to compare itself to 238, F. If that is >/</= I need it to write to 247,F. If 238, F is blank however, I need it to compare 246, F to 230 F. If that is >/</= I need it to write to 247, F. If that blank it needs to compare 246, F to 222, F... and so on and so on.

Until.

It compares 246, F to 22, F. If that is >/</= I need it to write to 247, F. If that is blank then it just needs to compare 246, F to 15, F. 15, F will always have a value.

That is the easiest example I could think of, but it needs to start from the last place the data was entered. So, for example, I won't always be starting at 246, F. I may be starting at 94, F and need that to compare back the same way.

So - what do I have so far?

Private Sub weights()
' Compare weights
Application.ScreenUpdating = False
Dim KSQ As Worksheet
Set KSQ = Worksheets("014633169324")

    Dim x As Integer
    For x = 6 To 105
    ' X is Columns
    
        If KSQ.Cells(22, x).Value = 0 Then
            KSQ.Cells(23, x).Value = "No"
            KSQ.Cells(23, x).Font.Bold = False
            KSQ.Cells(23, x).Interior.ColorIndex = -4142
        ElseIf KSQ.Cells(22, x).Value >= KSQ.Cells(15, x).Value Then
            KSQ.Cells(23, x).Value = "No"
            KSQ.Cells(23, x).Font.Bold = False
            KSQ.Cells(23, x).Interior.ColorIndex = -4142
        ElseIf KSQ.Cells(22, x).Value < KSQ.Cells(15, x).Value Then
            KSQ.Cells(23, x).Value = "Yes"
            KSQ.Cells(23, x).Font.Bold = True
            KSQ.Cells(23, x).Interior.Color = RGB(255, 0, 0)
        End If
        
    Dim y As Integer
    For y = 30 To 246 Step 8
    ' Y is Rows
    
        If KSQ.Cells(y, x).Offset(-8, 0).Value = 0 Or KSQ.Cells(y, x).Value = 0 Then
            KSQ.Cells(y, x).Offset(1, 0).Value = "No"
            KSQ.Cells(y, x).Offset(1, 0).Font.Bold = False
            KSQ.Cells(y, x).Offset(1, 0).Interior.ColorIndex = -4142
        ElseIf KSQ.Cells(y, x).Offset(-8, 0).Value <= KSQ.Cells(y, x).Value Then
            KSQ.Cells(y, x).Offset(1, 0).Value = "No"
            KSQ.Cells(y, x).Offset(1, 0).Font.Bold = False
            KSQ.Cells(y, x).Offset(1, 0).Interior.ColorIndex = -4142
        ElseIf KSQ.Cells(y, x).Offset(-8, 0).Value > KSQ.Cells(y, x).Value Then
            KSQ.Cells(y, x).Offset(1, 0).Value = "Yes"
            KSQ.Cells(y, x).Offset(1, 0).Font.Bold = True
            KSQ.Cells(y, x).Offset(1, 0).Interior.Color = RGB(255, 0, 0)
        End If

    Next y
    Next x
Application.ScreenUpdating = True
End Sub
So all this does right now, is compare whatever cell in the range I am using and whatever column I am using, to the one 8 cells before it; with a special case for 22 and 15.

This also, right now, creates a beastly amount of lag - though I have it set to only run when one of the target rows is interacted with. So when using the plentiful other fields those functions run smooth and undisturbed, and the 5 seconds of lag is only when entering a new weight.

I suppose that is another thing; showing the code pretty much sets what the main goal is. I essentially have 100 things that are being weighed at irregular intervals over 30 days, and need to show if weight is gained or lost.

Like I said earlier, I know what I ultimately want to accomplish can be accomplished, I just seem to be chasing my tail on /how/.