Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then" statement:

If I set "$A$#", this statement will return False after the 10th row. i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th and after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1 according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the whole
column B according to the whole column A" (i.e. A2 controls B2, A3 controls
B3, and so on.)
Thanks in advance.
Florence