Excel vba: Match the contents of three cells - then do find & replace
Hi! I have a workbook that includes two worksheets: "Open" and "COA TRACE". This is what I need to have happen:
1. "Open" has a beginning and ending range of numbers in columns M & N. In column J, there are event codes we are looking for.
2. "COA TRACE" shows all of the events (Col. G) that have happened for the number ranges(Cols. A & B).
3. I need a macro that will match up the number ranges and then look to see if col G in "COA TRACE" has any of the event codes listed in "Open" Col J for that number range.
4. Ideally, if the number ranges match and one of the event codes listed in "Open" Col J matches, then the cell in Col. J would be highlighted & the contents replaced with the event code that was found in "COA TRACE".
Example:
On "Open", row 2 I have a number range of 12345678910-12345678911, and I'm looking for events either 2960 or 2620.
When I look at "COA TRACE", row 3, the number range matches and the event 2620 is one that we are looking for.
At this point I would like Row 2, Col. J in "Open" to change to bright yellow and the "2960;2620" to be replaced with "2620".
Thank you in advance for any help or suggestions for better ways to handle this. I know its very complicated.
Experts-Exchange-Resolved-Events.xlsm
1. "Open" has a beginning and ending range of numbers in columns M & N. In column J, there are event codes we are looking for.
2. "COA TRACE" shows all of the events (Col. G) that have happened for the number ranges(Cols. A & B).
3. I need a macro that will match up the number ranges and then look to see if col G in "COA TRACE" has any of the event codes listed in "Open" Col J for that number range.
4. Ideally, if the number ranges match and one of the event codes listed in "Open" Col J matches, then the cell in Col. J would be highlighted & the contents replaced with the event code that was found in "COA TRACE".
Example:
On "Open", row 2 I have a number range of 12345678910-12345678911, and I'm looking for events either 2960 or 2620.
When I look at "COA TRACE", row 3, the number range matches and the event 2620 is one that we are looking for.
At this point I would like Row 2, Col. J in "Open" to change to bright yellow and the "2960;2620" to be replaced with "2620".
Thank you in advance for any help or suggestions for better ways to handle this. I know its very complicated.
Experts-Exchange-Resolved-Events.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome! Thank you SO much. I wish you knew how much time this will save me!
ASKER