I am trying to write a code that will look at when a cell is updated and loop through all previous sheets (there are 12 sheets total) and change all previous sheets in that same cell to the text (Yes, No, or N/A) that was in the cell that was just changed.
Example: In sheet8 i update cell E8 to "Yes"
I want sheet1 E8 to now be "Yes", sheet 2 E8 to be "Yes", sheet 3 E8 to be "Yes", sheet 4 E8 to be "Yes".......
I will start on E8 on sheet 1 and Say "Yes", so the only text in that will be yes. If I go to sheet 2 E8 and say "No", I want to change E8 on sheet one to be "No" now.
I am new to VBA and I have tried to google this stuff and this is what I have gotten.
Option Explicit
'Set to after update event
Private Sub object_AfterUpdate()
Public Sub printValue()
Dim E As String
E = ActiveCell.Address
If E = "Yes" Then printValue
If E = "No" Then printValue
If E = "N/A" Then Stop
End Sub
Public Function GetStartIndex() As Integer
On Error Resume Next
GetStartIndex = ThisWorkbook.Worksheets("I").Index - 1
End Function
Dim wks As Worksheet
Dim iEnd As Integer
Dim iStart As Integer
iStart = GetStartIndex()
iEnd = GetEndIndex()
If iStart > 0 And iEnd > 0 And iEnd > iStart Then
For I = iStart To iEnd
Set wks = ThisWorkbook.Worksheets(I)
MsgBox wks.Name
Dim CurCell_1 As Range
Dim CurCell_2 As Range
When E = "Yes"
Set CurCell_2 = iStart.ActiveCell()
For Each Mat In wks.ActiveCell
Set CurCell_2 = "Yes"
When C = "No"
Set CurCell_2 = iStart.ActiveCell()
For Each Mat In wks.ActiveCell
Set CurCell_2 = "No"
Public Function GetEndIndex() As Integer
On Error Resume Next
GetEndIndex = Sheet2.Worksheets.Index
End Function
Next I
End If
End Function
Bookmarks