Vba macro strange behaviour and wrong values.

[Buck123]

Hi, I am experiencing some strange issues using my macro for excel, I'm no professional coder but it reads like it should work okay. In fact it does work on the first cell and then the results are strange. I have a macro that when run on particular cell will color the surrounding cells green. Then look at the value in the cell directly above, then compare it to the value of the cell 3 rows up, and if it's lower than the value of the initial cell, it takes away the value of the cell above and also takes away -1. It does this for all cells around in 8 directions, north, south, east, west, southwest, southeast, northwest and northeast, except for SW,SE,NW and NE directions it takes away additional 1.4 instead of 1. When I do it for the first cell it works perfectly. Then when I run the macro on other cells it's not giving me the right value, and it doesn't color them properly. See code below:

Sub Fill()
    For Each cell In Selection
    'First Cell values'
        V = cell.Value
        N = cell.Offset(1, 0).Value
        S = cell.Offset(-1, 0).Value
        E = cell.Offset(0, -1).Value
        W = cell.Offset(0, 1).Value
        NE = cell.Offset(1, -1).Value
        NW = cell.Offset(1, 1).Value
        SE = cell.Offset(-1, -1).Value
        SW = cell.Offset(-1, 1).Value
       
       Cells(cell.Row - 1, cell.Column - 1).Resize(3, 3).Interior.Color = 5296274
       
    'Get Neighbor Cell Values'
    
        NN = cell.Offset(3, 0).Value
        NS = cell.Offset(-3, 0).Value
        NE = cell.Offset(0, -3).Value
        NW = cell.Offset(0, 3).Value
        NNE = cell.Offset(3, -3).Value
        NNW = cell.Offset(3, 3).Value
        NSE = cell.Offset(-3, -3).Value
        NSW = cell.Offset(-3, 3).Value
        
        
    'Fill North'
        If NN < (V - N - 1) Then
            Cells(cell.Row - 4, cell.Column - 1).Resize(3, 3).Interior.Color = 49407
            cell.Offset(3, 0).Value = (V - N - 1)
         End If
            
    'Fill South'
        If NS < (V - S - 1) Then
            Cells(cell.Row + 2, cell.Column - 1).Resize(3, 3).Interior.Color = 49407
            cell.Offset(-3, 0).Value = (V - S - 1)
         End If
         
    'Fill East'
        If NE < (V - E - 1) Then
           Cells(cell.Row - 1, cell.Column - 4).Resize(3, 3).Interior.Color = 49407
            cell.Offset(0, -3).Value = (V - E - 1)
          End If
          
    'Fill West'
        If NW < (V - W - 1) Then
            Cells(cell.Row - 1, cell.Column + 2).Resize(3, 3).Interior.Color = 49407
            cell.Offset(0, 3).Value = (V - W - 1)
          End If
          
    'Fill Northeast'
        If NNE < (V - NE - 1.4) Then
            Cells(cell.Row - 4, cell.Column - 4).Resize(3, 3).Interior.Color = 49407
            cell.Offset(3, -3).Value = (V - NE - 1.4)
         End If
         
    'Fill Northwest'
        If NNW < (V - NW - 1.4) Then
            Cells(cell.Row - 4, cell.Column + 2).Resize(3, 3).Interior.Color = 49407
            cell.Offset(3, 3).Value = (V - NW - 1.4)
          End If
          
    'Fill Southeast'
        If NSE < (V - SE - 1.4) Then
           Cells(cell.Row + 2, cell.Column - 4).Resize(3, 3).Interior.Color = 49407
            cell.Offset(-3, -3).Value = (V - SE - 1.4)
           End If
           
    'Fill Southwest'
        If NSW < (V - SW - 1.4) Then
           Cells(cell.Row + 2, cell.Column + 2).Resize(3, 3).Interior.Color = 49407
            cell.Offset(-3, 3).Value = (V - SW - 1.4)
           End If
           
    Next cell
End Sub

When I run it initially on the first cell it works fine, colours the main portions in green, and all the modified cells in yellow and values are correct. When I click on one of the new values and run it again, it colors the original 100 cells in yellow although it should remain green because it hasn't changed the value and I'm getting a misterious "5.2" in the lower left cell where it should obviously be "94.2". Same happens if I run the macro on the other 98. 6 cell.

I'm stumped here.


https://i.imgur.com/QN8F5ZG.png
https://i.imgur.com/ATZJvPp.png

Here's link to the spreadsheet.
https://www.dropbox.com/s/11r7io2tzjz89l7/algorithm test2.xlsm?dl=0

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm struggling to understand.
Normally, North is up, East right etc. Is your arrangement rotated 180 degrees?
For instance I presume this is getting a 'North' value but it is coming from the cell below the relevant cell.
N = cell.Offset(1, 0).Value

Also, when stepping through the section labelled 'Fill North', the code colours a range above the already coloured green section but fills in a value in the block below the green section.

Can you clarify what the code is actually supposed to be doing?

 

[Buck123]

Thanks for the input, yes I was absolutely wrong with the directions, it was all over the place, once I fixed them I found also that I used the same variable name twice "NE" and "NW" were used as both "Neighbor East, Neighbor West" and "Northeast and Northwest" so it was causing all kinds of unexpected results. I have it working perfectly now, cheers!

 

[Peter_SSs]

I have it working perfectly now, cheers!

Great news!

 

[RoryA]

Welcome to the forum.

For future reference, please read the forum rules on cross-posting, and follow them. Thanks.