making all occurrences of a value match the change made in one of those occurrences

[GAZZYMAN]

I had posted something similar to this on another site without getting any assistance. What I am asking for here provides less limitations and much more flexibility than what I had at the other site. The posting on the other site can be found using this link. http://www.msofficeforums.com/excel-...ange.html....I hope this will satisfy this sites requirements.

I am hoping to get a macro to do the following
Update multiple occurrences of cells to reflect a change in one of those occurrences

Column “H” can have various values such as “Yes, No, On Hold, On Board”. Validation is not applied to this column. I want to select any row based upon the person listed in Column “G” and edit the cell in column “H” of the same row. After making change in the Column “H” cell, I want to revise Column “H” for all other rows having same name of that person listed in column “H”to reflect that change.
Example:
If I revise the cell in “Row 9” Column “H” for “JO BLOW” from “NO” to “ON HOLD”, I would like to have all rows having “JO BLOW” in Column “H” changed so that Column “G” of those rows be updated to reflect the change to "ON HOLD" THAT WAS made in Row 9. These rows can be anywhere before or after row 9 such as rows 3 and 15.

[jindon]

Perhaps

To a sheet code module

Please Login or Register  to view this content.

[GAZZYMAN]

jindon,
Thanks for such a quick response to my posting. I will advise the results on my attempt to implement this solution. Thanks again.
GAZZYNAN

[GAZZYMAN]

jindon,

I inserted code into a sheet module by the following directions.
Copy the code that you want to use
Select the worksheet in which you the code to run
Right click on the sheet tab and click View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

I then made a change to cell located at column "H", Row "3" (changed "NO" to "YES"). This resulted in all the rows in column "H" changing from "NO" to "YES". I only want those rows to be changed that have the same value in column "G" as the cell in column "G", Row 3.


In the simple partial representation below.... Jo Blow shown in column "G", Row 3 was manually changed to "YES" and Jo Blow shown in column "G", Row 8 is the only row to be changed automatically to "YES".
Thanks again for your giving me of your time and the benefit of your skills
This an edited comment. The partial chart that I showed resulted in a garbled configuration. The intent was to have col "G" with heading "NAME" and col "H" with heading "CONTACT MADE"

COLUMN "G" COLUMN "H"
NAME CONTACT MADE
ROW 3 JO BLOW YES
ROW 4 R U COMING YES
ROW 5 GOOD BYE YES
ROW 6 HEL L LO YES
ROW 7 IM A COMMIN YES
ROW 8 JO BLOW YES
ROW 9 GOOD BYE YES

[jindon]

Example:
If I revise the cell in “Row 9” Column “H” for “JO BLOW” from “NO” to “ON HOLD”, I would like to have all rows having “JO BLOW” in Column “H” changed so that Column “G” of those rows be updated to reflect the change to "ON HOLD" THAT WAS made in Row 9. These rows can be anywhere before or after row 9 such as rows 3 and 15.

I though it would be "A"...
Try change to

Please Login or Register  to view this content.

[GAZZYMAN]

jindon,
Perfect....Thanks ever so much.

[jindon]

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[GAZZYMAN]

jindon,

Worked like a charm om my small sample spreadsheet.
However, I made a mistake in my description. On my small sample I used col "G" and Col "H" . The actual spreadsheet that will be using this macro actually uses Col "L" instead of Col "H". Consequently it did not work. Can you reach into your heart to make the code adjustment to reflect Col "L" instead of Col "H" ??

[jindon]

See the changes.

Please Login or Register  to view this content.

[GAZZYMAN]

I marked this thread as solved. I would ask you to indulge me one more time. I have a similar spreadsheet that uses Col H instead of Col G.
what would I change to accommodate that. Whether you choose to indulge me again or not, you have been a blessing for me. Thanks so much.

[jindon]

Change to suite.

Please Login or Register  to view this content.

[GAZZYMAN]

I am unclear by what you mean by change to suit. Using the code as is does not work for the spread sheet using col "H" and col "L". Do I select
Const NameCol As String = "G", or select ChangeCol As String = "H" (one or the other) in the second line of code??
Do I need to adjust more of the code??

[GAZZYMAN]

I think I understand "change to suit" .
Sorry for being such a dunce

[jindon]

I hope so...

[GAZZYMAN]

It works... You are a prince among men...take care of yourself cuz there are not many of us left. Thanks again

[GAZZYMAN]

jindon,

Trying to expand on the code you provided. I tried to change the sub name and "changed to suit" to include the same function for revising data in other columns. In addition to revising col "L" I thought it would be nice to have other macros that do the same for col "M" , "N" , etc. However when I copied the code and changed the sub names (so the subs will have unique names for the additional macros) , the macros did not work. Any thoughts ??

[jindon]

Changed the sub name?
You should not change it and post the code that you changed and explain what you are trying to do.

[GAZZYMAN]

Attempting to have same feature for updating data in other columns besides column "L" by having multiple sub routines, each dedicated to a column. I can not have subs with same name so I gave each sub a unique name.

Eample:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jindon]

You need to edit your post and wrap the code with the code tag
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

[jindon]

Can you edit your last post again and add /, forward slush in every second code tag to close, so that the code will be properly indented.

This is a strict forum rule.

[jindon]

Thanks.
Try change to

Please Login or Register  to view this content.

[GAZZYMAN]

syntax error 2nd line of code

[GAZZYMAN]

cancel last message, I found where I corrupted that line of code. Works great. THANK YOU AGAIN !!!

[jindon]

That's impossible.
Anyway, in your case,
ChangeCol As String = "L:L,N:N" should be

ChangeCol As String = "L:L,M:M" or
ChangeCol As String = "L:M" for consecutive columns

[GAZZYMAN]

WORKS GREAT....thanks a million

[GAZZYMAN]

jindon,

I am wondering if it is possible to further expand the capability of the ‘Private Sub ‘Worksheet_Change(ByVal Target As Range)

At this time I am set up to work with what I will refer to as “GROUP ONE” consisting of columns as follows:

Please Login or Register  to view this content.

I am wondering if it is possible to further expand the capability of the macro ‘Private Sub ‘Worksheet_Change(ByVal Target As Range) to include 2 additional groups consisting of columns as follows:

“GROUP TWO”

Please Login or Register  to view this content.

“GROUP THREE”

Please Login or Register  to view this content.

[GAZZYMAN]

Moving on...will take different approach regarding the three groups. Side note....One might wonder why I did not ask for the end product instead of working thru stages piecemeal. I can come up with numerous "excuses", however the real answer is that I did not have the vision to see that far past my immediate requirements. This dumb hillbilly is much closer to his goals by virtue of using this forum....and the good graces of jindon.
Thanks to jindon and all responsible for making this site available.

[GAZZYMAN]

After thought....The different approach I will taking is to manually adjust code to reflect which group I will be working on.

Please Login or Register  to view this content.

Maybe not the best solution but head and shoulders above what I started with.