[VBA] How change in each column from YES to NO to change YES or NO respectively in column H in the corresponding row ?

[vbalad]

Hi all,

I would like each change in each column from YES to NO to change YES or NO respectively in column H in the corresponding row.
I have this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MonitoredRange As Range
    Set MonitoredRange = Range("A1:C6")

    If Not Intersect(Target, MonitoredRange) Is Nothing Then
        Dim Cell As Range
        For Each Cell In Intersect(Target, MonitoredRange)
            If Cell.Value = "TAK" Then
                Call ChangeCellValue("TAK", Target.Row)
            ElseIf Cell.Value = "NIE" Then
                Call ChangeCellValue("NIE", Target.Row)
            End If
        Next Cell
    End If
End Sub

Sub ChangeCellValue(NewValue As String, RowToChange As Long)
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells(RowToChange, "H").Value = NewValue 
    Next ws
End Sub

 

[Joe4]

So, how is your code NOT working now?
Can you show us a sample of your data, walk us through an example, and tell us what the code is currently doing, and what it should be doing in that case?

 

[vbalad]

Hi,

Here is example:

test_macro_intersect.xlsm
	A	B	C	D	E	F	G	H
1	NIE	TAK	TAK					TAK
2	NIE	TAK	NIE					TAK
3	TAK	NIE	TAK					TAK
4	TAK	TAK	TAK					TAK
5	TAK	TAK	TAK					TAK
6	NIE	TAK	TAK					TAK
Arkusz1

Cells with Data Validation
Cell	Allow	Criteria
H1:H6	List	TAK;NIE
A1:C6	List	TAK;NIE

When I change something happens nothing

Regards

 

[Joe4]

It works just fine for me.

What is the name of the module you have placed this VBA code in?
It needs to be in the particular Sheet module (all "Worksheet" event procedures need to be in the worksheet module of the sheet you want it to run in).
If it is in any other module, it will NOT work automatically.

 

[vbalad]

Hi,

Works. You're right, Joe4
I had placed in "This Workbook" instead of in "Sheet1". My English is pretty good, but mistakes happen

Regards