hy guys,
I have this code
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myMax As Long, x, ws As Worksheet
If (Sh.Name = "Master") + (Sh.Name = "Numbers") Then Exit Sub
If Intersect(Sh.Columns("b"), Target) Is Nothing Then Exit Sub
If Target.Count > 1 Then
With Application
.EnableEvents = False
MsgBox "You can not change multiple cells in Column B at a time", vbCritical
.Undo
.EnableEvents = True
Exit Sub
End With
End If
Application.EnableEvents = False
Target.Offset(, -1).Resize(, 20).Interior.ColorIndex = xlNone
If (Target.Value = "x") * (Target.Offset(, -1).Value = "") Then
For Each ws In Worksheets
If (ws.Name <> "Master") * (ws.Name <> "Numbers") Then
myMax = Application.Max(myMax, Application.Max(ws.Columns(1)))
End If
Next
Target.Offset(, -1).Value = myMax + 1
Sheets("master").Range("a" & Rows.Count).End(xlUp)(2).Resize(, 2).Value = Target.Offset(, -1).Resize(, 2).Value
ElseIf (Target.Value = "") * (Target.Offset(, -1).Value <> "") Then
x = Application.Match(Target.Offset(, -1), Sheets("master").Columns(1), 0)
If IsNumeric(x) Then Sheets("master").Range("a" & x).Resize(, 2).Interior.Color = vbRed
Target.Offset(, -1).Resize(, 20).Interior.Color = vbRed
ElseIf (Target.Value = "x") * (Target.Offset(, -1).Value <> "") Then
x = Application.Match(Target.Offset(, -1), Sheets("master").Columns(1), 0)
If IsNumeric(x) Then
Sheets("master").Range("a" & x).Resize(, 20).Interior.ColorIndex = xlNone
Else
Sheets("master").Range("a" & Rows.Count).End(xlUp)(2).Resize(, 2).Value = _
Target.Offset(, -1).Resize(, 2).Value
End If
End If
Application.EnableEvents = True
End Sub
If i put "x" in column B from Sheet1, Sheet2, Sheet3 then in column A is allocated a unique number and is copied in sheet Master. Is possible to implement in this code next request :if i put "y" in column B, then in column A is allocated "x" value.1 (x.1,x.2,x.3 etc) ant copy in Master?
i will give an example in sheet3
Bookmarks