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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

mfairbanks

New Member
Joined
Sep 12, 2002
Messages
17
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"
 

mfairbanks

New Member
Joined
Sep 12, 2002
Messages
17

ADVERTISEMENT

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?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

mfairbanks

New Member
Joined
Sep 12, 2002
Messages
17

ADVERTISEMENT

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?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

mfairbanks

New Member
Joined
Sep 12, 2002
Messages
17
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?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,143,706
Messages
5,720,384
Members
422,282
Latest member
psunith

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
Top