Formula works for one row. But I need it to work with all ro

mfairbanks

New Member
Joined
Sep 12, 2002
Messages
17
I am using VBA to have some cells autofill if they are = No. I set it up, if cell A2 = No then cells B2 through F2 Value will = No.
How can I set this up to work on every row for each time the select No in column A is selected?

Here is the formula:
If Target.Address = "$A$2" Then

If LCase(Target.Value) = "no" Then
Range("$B$2:$F$2").Value = "No"
End If
End If
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Time to go home, so I'm guessing it's in your Worksheet_Change procedure. So try this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If LCase(Target.Value) = "no" Then
            Target.Offset(0, 1).Resize(1, 5).Value = "No"
        End If
    End If
End Sub
 
Upvote 0
Yes! That worked GREAT. Thank you. I am new to this. Is the offset (Row,column) then resize (Row,Column)?

Target.Offset(0, 1).Resize(1, 5).Value = "No"
 
Upvote 0
When i'm in Excel and I hit the delete key in that cell. It gives me runtime error 13 and highlites:

If LCase(Target.Value) = "no" Then

What does this mean?
 
Upvote 0
From your first question I assume you are having difficulty reading my code. I agree that sometimes characters in the Courier font are unclear.

You should copy the code from this Message Board. To do so open your workbook, switch to the VBE and choose the module for your sheet. Then open your posting on the Message Board. With the mouse, select all the text in the code and press Ctrl+Insert. Return to your module and press Shift+Insert.

With my code I could not reproduce the Type 13 error by pressing the Delete key.
 
Upvote 0
I am able to use the formula, it works great. But, if I choose all the cells that say No and hit delete, I then get the error?
 
Upvote 0
Insert the following as the first line of code:

If Target.Count > 1 Then Exit Sub

Now the procedure will stop if more than one cell is changed. This may not be what you want - if so post back with more details of what you are trying to achieve.
 
Upvote 0
You are the man! That did it.

Another question. I sent this file to someone else. It is not working on their computer? I also tried it on another computer by my desk. It only seems to work on my computer. Why is that?
 
Upvote 0
Just a guess.

What is the Security level on the other machines (Tools, Macro, Security)? If it is High macros are automatically disabled when the workbook is opened.
 
Upvote 0

Forum statistics

Threads
1,218,575
Messages
6,143,313
Members
450,477
Latest member
teresab543

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top