Cell value that changes previously entered value

[rtour957]

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 b
changed to show 'F1 rejected', P1 rejected' and so on.
Any help with VBA code (as I understand is needed) would be greatl
appreciated.
Entering 'YES' should not change B1:B4 existing value.

Thanks, Ar

 

[GS]

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

 

[rtour957]

;1609145']What happens if you change your "NO" entry? I'm thinking i
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 providin
VBA fo this.
However, when I pated this into the worksheet it did not work. I'
absolutely ignorant in VB and all I could figure out was: there's
missing quote in ("B1:B4). But even when I corrected that it still no
working. Am I supposed to add something else to this code, like fill i
blanks or do anything else?

Thanks, Ar

 

[GS]

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 Insert>Module 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

 

[rtour957]

;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 Insert>Module 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|
+-------------------------------------------------------------------+

 

[GS]

rtour957 formulated on Tuesday :

;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 Insert>Module 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.

But, IMO, you might be better of to use Conditional Formatting to
'flag' rejections rather than edit cell content. This way you never
lose the original data but still know whether it got rejected or not.
To implement a 'flag' in B1:B4, just have CF change the cell color.
Select B1:B4 and use the following formula in the CF condition criteria
box...

=UPPER($A1)="NO"

...and click the 'Format...' button to set a dotted border outline and
red pattern. (The dotted border will simulate gridlines if contiguous
cells are flagged)

Optionally, you could do both!

--
Garry

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

 

[rtour957]

;1609312']rtour957 formulated on Tuesday :-

'GS[_2_ Wrote: -

;1609211']Sorry.., I wasn't aware of your skills level. Yes, th code
does not
work "as is" and so here is a complete macro that you can paste int a
standard module. To do this, open the VBE (Alt+F11) and right-click
your project (workbook name listed in the Project Explorer pane) an
select Insert>Module from the context menu. Then paste this sub int
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 i
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 cell 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". No
when I do - the first script it gives me "#name?" in celLs B1:B4 and th
second code highlight in yellow the first line and gives a syntax erro
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[GS]

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

 

[rtour957]

;1609390']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 an the
second code highlight in yellow the first line and gives a synta 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.

Ar

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[GS]

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