User to click on a cell with excel Hyperlink function to another sheet's selected cell range(change selection background colour)

Mad4xcel

New Member
Joined
Mar 26, 2022
Messages
5
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. 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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,883
Messages
6,175,168
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top