Change cell by clicking on another cell

[Closed]
Report
Posts
3
Registration date
Monday June 29, 2015
Status
Member
Last seen
July 6, 2015
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

I'm looking for a Macro/Formula to change value of a cell to (the row of the original cell I click on - 2). Is this possible?

Cheers all,

2 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Sedicee,

So you want to change the value of the cell, two cells left of the chosen cell.

This could be done by double clicking the cell (to auto run the macro), but what change to the cell needs to be made?

Best regards,
Trowa
Posts
3
Registration date
Monday June 29, 2015
Status
Member
Last seen
July 6, 2015

I have a title page and a sheet that displays data indexed from other sheets. I would like to be able to double click a title and have a cell that changes it's value to the number of the title so that the indexed display sheet will change it's information to what I what I clicked on.
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Sedicee,

Could you give an example with cell references and to what range it applies?

Best regards,
Trowa
Posts
3
Registration date
Monday June 29, 2015
Status
Member
Last seen
July 6, 2015

Thanks for your help,

Here's a link to an example document if that makes it easier: https://drive.google.com/file/d/0B-2g_fp7BNE1VVdFNzBERUQ5aE0/view?usp=sharing

I'm looking for a way to make the "Subject Number" cell to update to the number of a subject that I click on, so that the display sheet will update to the corresponding data from the datasheet.

Cheers,

Sedicee
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Sedicee,

That definitely helps.

Right-click the Display Sheet's tab and select View Code, then paste the following code there:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("C2:C" & Rows.Count)) Is Nothing Then Exit Sub
Range("H2").Value = Target.Offset(0, -1).Value
End Sub


Now double-click on a subject in column C and watch the magic happen.

Best regards,
Trowa