Automatically change cell value when another cells value change to 0

[OX_2005]

I am still very new to VBA and I am struggling with this. Any assistance will be helpful.

When the value in Column "M" changes to "0" I am wanting the values in columns "E:I" to automatically change to "FALSE" so that the check box will uncheck. I can get it do work with just 1 row of data but I am trying to get it to do a Range of rows (3 to 42).

This is the current code I put together

This is the Doc I am working with

 

[Joe4]

Do you want this to happen automatically, as column M is being updated?
Or are you wanting to run this on existing data all at once?

 

[Joe4]

If you want it to work automatically as you are updated the values in cells M3:M42 manually, then put this code in the Sheet module in VBA:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if any values in column M were updated
    Set rng = Intersect(Target, Range("M3:M42"))
    
'   Exit if no cells in that range were updated
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells in watched range can check their values
    Application.EnableEvents = False
    For Each cell In rng
'       See if value is 0
        If cell.Value = 0 Then
'           Update columns E through I
            Range(Cells(cell.Row, "E"), Cells(cell.Row, "I")).Value = "FALSE"
        End If
    Next cell
            
    Application.EnableEvents = True

End Sub

This will run automatically as you manually update values in column M.

 

[OX_2005]

Do you want this to happen automatically, as column M is being updated?
Or are you wanting to run this on existing data all at once?

I am wanting it to update automatically as column M is being updated

 

[Joe4]

I am wanting it to update automatically as column M is being updated

Then the code I provided in my previous post should work for you.

Note: If you need to differentiate literal zeroes in column M from blanks (empty cells), so that blanks are NOT treated like zeroes, then change this line:

        If cell.Value = 0 Then

to this:

        If (cell.Value = 0) and (Len(cell) = 1) Then

 

[OX_2005]

If you want it to work automatically as you are updated the values in cells M3:M42 manually, then put this code in the Sheet module in VBA:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   See if any values in column M were updated
    Set rng = Intersect(Target, Range("M3:M42"))
   
'   Exit if no cells in that range were updated
    If rng Is Nothing Then Exit Sub
   
'   Loop through cells in watched range can check their values
    Application.EnableEvents = False
    For Each cell In rng
'       See if value is 0
        If cell.Value = 0 Then
'           Update columns E through I
            Range(Cells(cell.Row, "E"), Cells(cell.Row, "I")).Value = "FALSE"
        End If
    Next cell
           
    Application.EnableEvents = True

End Sub

This will run automatically as you manually update values in column M.

Will this work if Column M is a Formula?

 

[Joe4]

Will this work if Column M is a Formula?

Nope. Worksheet_Change event procedures only run when cells are manually updated.
However, what is the formula in column M?
Are the cells that the formula in column M uses updated manually?
If so, we can watch those instead.

 

[OX_2005]

Nope. Worksheet_Change event procedures only run when cells are manually updated.
However, what is the formula in column M?
Are the cells that the formula in column M uses updated manually?
If so, we can watch those instead.

Since it has to be a manual update I changed the column from M to C and the Value to "" (blank) and its working

 

[OX_2005]

Since it has to be a manual update I changed the column from M to C and the Value to "" (blank) and its work

 

[Joe4]

Do you have some follow-up question?
From your last comment, it appears that you are saying that you got it to work for you.