Replace cell values in multiple columns

[Jen_T]

I have a worksheet that has 51 columns, each column is a state label, that
shows a status of "1" or "0". This includes is a column that indicates "ALL
STATES". I am looking to see how to write a macro that would look at "All
States" (column M) and if "1" than look in same row for each state and
replace if remaining states are equal to "1" (1- 50) to a "0"
Then do this for each row.
Example:
ALL STATES AK AL CA CO CT
1 1 1 0 1 0

Final Results
ALL STATES AK AL CA CO CT
1 0 0 0 0 0

Not sure how to write this in code.

 

[Sam Wilson]

Assuming you have "All States" in column A, right click your tab and view
code and then paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

If Not Intersect(Target, Columns("A:A")) Is Nothing And Target.Value = 1 Then
For i = 1 To 50
Target.Offset(0, i).Value = 0
Next i
End If

End Sub

 

[Sam Wilson]

Sorry, I mean paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo errorcatcher

Dim i As Integer

If Not Intersect(Target, Columns("A:A")) Is Nothing And Target.Value = 1 Then
For i = 1 To 50
Target.Offset(0, i).Value = 0
Next i
End If

errorcatcher:
Application.EnableEvents = True

End Sub

Much safer.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Change_State()
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "M").End(xlUp).Row
Set MyRange = Range("M2:M" & lastrow)
For Each c In MyRange
If c.Value = 1 Then
c.Offset(, 1).Resize(, 50).Value = 0
End If
Next
End Sub

Mike