Display a cell value on another sheet from a range of cells

Josh14

New Member
Joined
Jan 22, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
OK, here's the scenario I would like to solve. I have multiple sets of data on Sheet1

Sheet1
Set 1 is in A1:A6
Set 2 is in A8:A20
Set 3 is in A22:A30

I would like to be able to do the following:

Click on a cell in Set 1 and it populate cell A1 on Sheet 2.
Click on a cell in Set 2 and it populates cell A2 on Sheet 2.
Click on a cell in Set 3 and it populates cell A3 on Sheet 2.

I would also like the color to change when I click on the cell in Sheet 1. So if I click on A2 on Sheet 1 then A2 on Sheet 1 turns green and whatever is in that cell is now displayed on Sheet 2, A1. But if I reselect say A4, now A2 goes to no color and now A4 is green and that is what is displayed on Sheet 2, A1.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
see if this does what you want. Copy the code to sheet1 code module

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A6")) Is Nothing Then
    Sheets("Sheet2").Range("A1") = Target.Value
ElseIf Intersect(Target, Range("A8:A20")) Is Nothing Then
    Sheets("Sheet2").Range("A2") = Target.Value
ElseIf Intersect(Target, Range("A22:A30")) Is Nothing Then
    Sheets("Sheet2").Range("A3") = Target.Value
End If
End Sub
 

Josh14

New Member
Joined
Jan 22, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Beautiful, thanks JLGWhiz! This takes care of everything except the color change which would be a nice indicator of which cell from each set I have selected. Any way to accomplish this?
 

Josh14

New Member
Joined
Jan 22, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I was able to answer my own question eventually - the below code will work for the colors.

Setting the color index to 2 on the range resets everything to blank every time a cell is clicked. And then it will change the specific cell that is selected to green (4) as the last thing.

Thanks again for the help JLG!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B36:B39")) Is Nothing Then
Sheets("Sheet2").Range("A1") = Target.Value
Sheets("Sheet1").Range("B36:B39").Interior.ColorIndex = 2
Target.Interior.ColorIndex = 4
ElseIf Not Intersect(Target, Range("B47:B79")) Is Nothing Then
Sheets("Sheet2").Range("A2") = Target.Value
Sheets("Sheet1").Range("B47:B79").Interior.ColorIndex = 2
Target.Interior.ColorIndex = 4
ElseIf Not Intersect(Target, Range("B84:B94")) Is Nothing Then
Sheets("Sheet2").Range("A3") = Target.Value
Sheets("Sheet1").Range("B84:B94").Interior.ColorIndex = 2
Target.Interior.ColorIndex = 4
ElseIf Not Intersect(Target, Range("B99:B101")) Is Nothing Then
Sheets("Sheet2").Range("A4") = Target.Value
Sheets("Sheet1").Range("B99:B101").Interior.ColorIndex = 2
Target.Interior.ColorIndex = 4
End If
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Thanks for the feedback. You need to learn how to put code tags on so when you post code it will be formatted as code instead of plain text. Here's how:
1. paste or type the code into the reply box.
2. Use the mouse pointer to select only code.
3. Click the VBA Icon on the tool bar
4. Click the 'Post Reply' button

If you want to highlight font in the code to emphasize or illsustrate an issue, then click the RICH icon instead of the VBA icon and you can select the font colors from the palette icon located in front of the big A on the tool bar. The difference between the VBA an d RICH code is that VBA follows the attributes of the vb editor with colors, etc. The RICH does not display colors unless the user puts them in.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,128,119
Messages
5,628,799
Members
416,340
Latest member
PJB1102

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
Top