Autocomplete

[Worzel Gummidge]

I In column I, I have a dropdown menu listing either "yes" or "no".

What I would like to do is, when any other cell within that row contains
data, for the cell in colum I to change automatically to "no" until user
changes it to "yes"

So for example, if I enter any data into cells A1 or B1 or C1 etc etc, then
cell I1 will automatically fill with the word "no"

Any suggestions? I have been told I can do this with a worksheet_change
event but ain't got the foggiest.

 

[Tom Ogilvy]

Right click on the sheet tab and select view code. In the resulting module,
in the upper left dropdown, select Worksheet and in the upper right dropdown
select Change (not selectionchange)

this will cause the declaration of the Change event of which you speak:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

so within this declaration you would put code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If target.count > 1 then exit sub
if target.column >=1 and target.column <= 3 then
if application.CountA(cells(target.row,1).Resize(1,3)) > 0 then
cells(target.row,"I").value = "no"
end if
end if
End Sub

 

[Worzel Gummidge]

Thanks Tom

Right click on the sheet tab and select view code. In the resulting
module,
in the upper left dropdown, select Worksheet and in the upper right
dropdown
select Change (not selectionchange)

this will cause the declaration of the Change event of which you speak:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

so within this declaration you would put code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If target.count > 1 then exit sub
if target.column >=1 and target.column <= 3 then
if application.CountA(cells(target.row,1).Resize(1,3)) > 0 then
cells(target.row,"I").value = "no"
end if
end if
End Sub