VBA fill inbetween two points

Hi, kinda a noobie at VBA here. I am trying to get, on a button press, to have the cells between two points be filled with just a 1 when i press a button on the worksheet. The two points are different each time and the cells have formulas in them which is what defines the start and end points so the function will have to overwrite these. I am basically trying to achieve this at the press of a button and just for the fill to right one.

Any help is greatly appreciated

You will need to provide more information. Are the 2 cells in she same column, same row, or random? Will you use your mouse to highlight the cells before you trigger the macro? Would the formulae be replaced with a 1, or kept?

example.png

Similar to that picture. I need to fill in all the red blocks with one click. The only cells in the sheet with just a 1 in it will be the ones that need doing. I don't need to keep the formula it just needs to be overwritten.

Why don't you just highlight the range where you want the 1s, type a 1 and then press Ctrl-Enter No Vba required

Because this sheet goes on for over 100 rows and none of them are in line

try

Sub test()
Dim i As Long, rng As Range, x As Range
With ActiveSheet.UsedRange
    .Interior.ColorIndex = xlNone
    For i = 1 To .Rows.Count
        On Error Resume Next
        Set rng = .Rows(i).SpecialCells(2)
        On Error GoTo 0
        If Not rng Is Nothing Then
            If x Is Nothing Then
                Set x = rng(1).Resize(, .Columns.Count) _
                .SpecialCells(4).Areas(1)
            Else
                Set x = Union(x, rng(1).Resize(, _
                .Columns.Count).SpecialCells(4).Areas(1))
            End If
        End If
    Next
    If Not x Is Nothing Then x.Interior.ColorIndex = 3
End With
Set rng = Nothing
Set x = Nothing
End Sub

This is one of the things I like about helping on these forums, is you always learn something new. Never hear of the specialcells method before, nor the usedrange property. Both very useful tools