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.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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