Dear All,
I receive a spreadsheet where B1:B4 has either of F1, P1, F9, P9.
Column A is for me to enter either 'YES' or 'NO'.
If I enter 'NO' in any of A1:A4 I need a corresponding B1:B4 cell be changed to show 'F1 rejected', P1 rejected' and so on.
Any help with VBA code (as I understand is needed) would be greatly appreciated.
Entering 'YES' should not change B1:B4 existing value.

Thanks, Art

What happens if you change your "NO" entry? I'm thinking it would be
better to have VBA put a formula in B1:B4 so if you 'Undo' your input
the cell will revert back to its original value...

Dim c As Range, sz$, lRow&

For Each c in Range("B1:B4)
sz = c.Text: lRow = c.Row
c.Formula = _
"=IF($A" & lRow & "=""NO""," & sz & " rejected," & sz & ")"
Next 'c

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Appreciate your thoughtfulness on what happens if down the road "No" changes to "Yes". This is really good point and thank you for providing VBA fo this.
However, when I pated this into the worksheet it did not work. I'm absolutely ignorant in VB and all I could figure out was: there's a missing quote in ("B1:B4). But even when I corrected that it still not working. Am I supposed to add something else to this code, like fill in blanks or do anything else?

Thanks, Art

Sorry.., I wasn't aware of your skills level. Yes, the code does not
work "as is" and so here is a complete macro that you can paste into a
standard module. To do this, open the VBE (Alt+F11) and right-click
your project (workbook name listed in the Project Explorer pane) and
select InsertModule from the context menu. Then paste this sub into
the code window that appears...

Sub RejectAdjacentData()
Dim c As Range, sz As String, lRow As Long

For Each c in Range("B1:B4")
sz = c.Text: lRow = c.Row
c.Formula = _
"=IF($A" & lRow & "=""NO""," & sz & " rejected," & sz & ")"
Next 'c
End Sub

Thanks for catching the missing quotes!
To use the macro enter Alt+F8 on the keyboard and select the macro in
the list, then click 'Run' to execute.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I pasted the macro into the VBE but putting "no" to any of the cells in A does not add the string "rejected" to the existing value in B. I attached the file I had the macro in. Would be able to check why it's not working?

Thank you for your patience,
Art

rtour957 formulated on Tuesday :
'GS[_2_ Wrote:
;1609211']Sorry.., I wasn't aware of your skills level. Yes, the code
does not
work "as is" and so here is a complete macro that you can paste into a
standard module. To do this, open the VBE (Alt+F11) and right-click
your project (workbook name listed in the Project Explorer pane) and
select InsertModule from the context menu. Then paste this sub into
the code window that appears...

Sub RejectAdjacentData()
Dim c As Range, sz As String, lRow As Long

For Each c in Range("B1:B4")
sz = c.Text: lRow = c.Row
c.Formula = _
"=IF($A" & lRow & "=""NO""," & sz & " rejected," & sz & ")"
Next 'c
End Sub

Thanks for catching the missing quotes!
To use the macro enter Alt+F8 on the keyboard and select the macro in
the list, then click 'Run' to execute.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I pasted the macro into the VBE but putting "no" to any of the cells in
A does not add the string "rejected" to the existing value in B. I
attached the file I had the macro in. Would be able to check why it's
not working?

Thank you for your patience,
Art


+-------------------------------------------------------------------+
Filename: F1 rej.zip |
Download: http://www.excelbanter.com/attachment.php?attachmentid=759|
+-------------------------------------------------------------------+

Entering "no" doesn't 'trigger' the macro. You must 'run' the macro
after entering "no". You did not put the code in a standard module as
instructed, but not to worry..! To have Excel run the macro
automatically you need to right-click the sheet tab, choose 'View
Code', and replace the previous code with the following...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then Call
RejectAdjacentData(Target)
End Sub

Sub RejectAdjacentData(Rng As Range)
Dim sz$, sr$, sFormula$

With Rng.Offset(0, 1)
sz = .Text: sr = CStr(.Row)
sz = Replace(sz, " rejected", "") '//in case already done
sFormula = "=IF(UPPER($A" & sr & ")=""NO""," '//set condition
sFormula = sFormula & Chr(34) & sz & " rejected""," '//=condition
true
sFormula = sFormula & Chr(34) & sz & Chr(34) & ")" '//=condition
false
.Formula = sFormula
End With
End Sub

...so IF you enter "no" you WILL be able to 'undo' it after it changes
B.

Sorry, I did not realize macro has to be run after entering "no". Now when I do - the first script it gives me "#name?" in celLs B1:B4 and the second code highlight in yellow the first line and gives a syntax error on "If Not Intersect(Target, Range("A1:A4")) Is Nothing Then Call" line. Words "True" and "False" down below are in red as well.
Any ideas what's going on wrong?

Thanks, Art.

rtour957 expressed precisely :
Sorry, I did not realize macro has to be run after entering "no". Now
when I do - the first script it gives me "#name?" in celLs B1:B4 and the
second code highlight in yellow the first line and gives a syntax error
on "If Not Intersect(Target, Range("A1:A4")) Is Nothing Then Call" line.
Words "True" and "False" down below are in red as well.
Any ideas what's going on wrong?

Thanks, Art

Well.., you probably have some word wrap (every line is a single
statement and so your reader wrapped some of the words to a new line)
happening if you did a copy/paste. For instance, the code in the
Worksheet_Change sub is a single line. You'll need to backspace
"RejectAdjacentData(Target)" so it follows the line above like so...

If Not Intersect... Then Call RejectAdjacentData(Target)

...so the entire statement is one line.

Do the same for the "true" and "false" as well as those belong to the
comment after the line above each word...

sFormula = sFormula &... '//=condition true
sFormula = sFormula &... '//=condition false

Let me know how this works out! I tested the code (as well as the
Conditional Formatting suggestion I made) until I was
'blue-in-the-face', seems like, and it worked really nice.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks Gary,
Works like a magic and does exactly what I was looking for.
Sorry, I'm comletely ignorant in VB.

Art

Thanks for the feedback. Glad you got it working! Always happy to be of
help when I can!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion