Mad4xcel
New Member
- Joined
- Mar 26, 2022
- Messages
- 5
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
- MacOS
- Mobile
- Web
Hi All,
I have two sheets,
Sheet 1( has Id's and email addresses columns, Each Id has many emails, sometimes blanks too,. Each Id is duplicated into multiple rows in Sheet 1. This is from a database into which emails were put in through a manual process, sometimes with errors and sometimes none, blanks result from it.
Sheet 3 is my report and it has Id's, and Email addresses columns. Each Id is a unique row in Sheet 3.
Sheet 3 ID column has another ID column duplicated(into which the ID is presented as a hyperlink to the Sheet 1, the hyperlink can point to one row of sheet 1 or a cell range in Sheet 1. for example A2:Z2 in case of a single row(if ID in sheet1 is unique) or A4:Z10 in case of a Cell range (if ID in sheet1 is duplicated in multiple rows).
IN the second ID column(duplicate) of Sheet 3 below formula is present:
=HYPERLINK(CONCATENATE("#'Sheet1'!A",MATCH($A2,'Sheet1'!$A:$A,0),":Z",SUMPRODUCT(MAX(ROW('Sheet1'!$A:$A)*($A2='Sheet1'!$A:$A)))),$A2)
User can click on a cell with excel Hyperlink function which will take him to Sheet 3's selected row or cell range.
I would like to change selection background color when the hyperlink leads a user from sheet 3 to sheet 1's selected cell range, as long as he is in range, the background color should be of a different color , example yellow. once the user click's on another sheet(worksheet change event for example, revert the back ground color of selected cell range back to the original(which is no fill))
Thank you very much for any suggestions or ideas.
Best Regards.
I have two sheets,
Sheet 1( has Id's and email addresses columns, Each Id has many emails, sometimes blanks too,. Each Id is duplicated into multiple rows in Sheet 1. This is from a database into which emails were put in through a manual process, sometimes with errors and sometimes none, blanks result from it.
Sheet 3 is my report and it has Id's, and Email addresses columns. Each Id is a unique row in Sheet 3.
Sheet 3 ID column has another ID column duplicated(into which the ID is presented as a hyperlink to the Sheet 1, the hyperlink can point to one row of sheet 1 or a cell range in Sheet 1. for example A2:Z2 in case of a single row(if ID in sheet1 is unique) or A4:Z10 in case of a Cell range (if ID in sheet1 is duplicated in multiple rows).
IN the second ID column(duplicate) of Sheet 3 below formula is present:
=HYPERLINK(CONCATENATE("#'Sheet1'!A",MATCH($A2,'Sheet1'!$A:$A,0),":Z",SUMPRODUCT(MAX(ROW('Sheet1'!$A:$A)*($A2='Sheet1'!$A:$A)))),$A2)
User can click on a cell with excel Hyperlink function which will take him to Sheet 3's selected row or cell range.
I would like to change selection background color when the hyperlink leads a user from sheet 3 to sheet 1's selected cell range, as long as he is in range, the background color should be of a different color , example yellow. once the user click's on another sheet(worksheet change event for example, revert the back ground color of selected cell range back to the original(which is no fill))
Thank you very much for any suggestions or ideas.
Best Regards.