Hello Norman, Bob and Jim,

I do appreciate for the sharing from ALL OF YOU. As a VBA beginner, I
enjoy all of your suggestions so that I get better understanding for various
methods.

Thank you for your contribution.

Best wishes,

Florence

"Norman Jones" wrote:

Hi Bob,

Why be clear when you can be obtuse :-)?

I accept no plaudits for clarity, and no accusation of obtuseness - the
code is not mine.

There are enough examples of my obtuseness for it to be unnecessary to
ascribe to me the imagined failings of others. <VBG

I merely drew the OP's attention to the fact that the author's code worked
in its unabridged form.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
Why be clear when you can be obtuse :-)?

What is wrong with

If Target.Column = 1 Then

no chance then of forgetting the syntax

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Norman Jones" wrote in message
...
Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in this
case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
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