Change On_Open macro to a cell change in a range of cells

[davidpierce]

Hi all,
Is there a way to get the following code to fire once a cell changes in a range of M10:M (if any cell changes from M10 down to "end of project" ?

Private Sub Workbook_Open()
Dim i As Long
                With Sheets("Sheet1")
i = 2:      Do Until .Range("B" & i) = "End of Project"
If .Range("C" & i) < 1 And .Range("D" & i) <> "" Then _
        .Range("E" & i) = Range("A1")
            i = i + 1:  Loop
                End With
 End Sub

Thanks everyone!
Dave

[Kaper]

in thisworkbook code:

Private Sub Workbook_Open()
call looping_till_EOP
End Sub

in Sheet1 code

Private Sub Worksheet_Change(ByVal Target As Range)
if not intersect(target,range("M10:M"&rows.count)) is nothing then
call looping_till_EOP
end if
End Sub

in normal module

sub looping_till_EOP()
Dim i As Long
                With Sheets("Sheet1")
i = 2:      Do Until .Range("B" & i) = "End of Project"
If .Range("C" & i) < 1 And .Range("D" & i) <> "" Then _
        .Range("E" & i) = Range("A1")
            i = i + 1:  Loop
                End With
end sub

[davidpierce]

Thank you Kaper!
Works just wonderful~ I will call this solved
Right out of the box even
Dave

[davidpierce]

Hi all,
This worked fine in a sample but now that I am putting it into my actual working schedule I cant get it to work? The only difference is basically I have cells with calculations/formulas in them. Is there a way to make this so it works and skips the cells with formulas in "M and N"
Code is below, and I attached a sheet!

This code when wb_open auto adds today's date in "N" if a date is in "M". When I put a date in "M" it auto puts today's date (F6) into "N". And adds today, each time sheet is opened or changed till 100% complete in "G"



This workbook Code:

Private Sub Workbook_Open()

call looping_till_EOP

End Sub

Sheet 1 Code:

Private Sub Worksheet_Change(ByVal Target As Range)

if not intersect(target,range("M12:M"&rows.count)) is nothing then

call looping_till_EOP

end if

End Sub

In Normal Module:

sub looping_till_EOP()

Dim i As Long

                With Sheets("Sheet1")

i = 12:      Do Until .Range("B" & i) = "End of Project"

If .Range("G" & i) < 1 And .Range("M" & i) <> "" Then _

        .Range("N" & i) = Range("F6")

            i = i + 1:  Loop

                End With

end sub

[Kaper]

Check if these two changes:

Do Until ucase(.Range("B" & i)) = "END OF PROJECT"

and

If .Range("G" & i) < 1 And .Range("M" & i).value <> "" and _
  not(.Range("M" & i).hasformula) and not(.Range("N" & i).hasformula) Then _
        .Range("N" & i) = Range("F6")

help

[davidpierce]

That seamed to work just fine. Thank you again! I will test it tomorrow when the date changes and see if the 100% stops too!
Thanks so much
Dave