Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

change value of specific cell based on which cell is selected


I'm working on a project in excel, but my knowledge of the program is fairly limited. My current project is built around a table with 13 columns and about 100 rows. I want a cell outside of the table to display the value of a different cell in the same row as the cell I currently have selected. For example, when I select cell A5 (in the table) I want the cell O3 (not in the table), to display the value of the cell M5 (in the table, but hidden).

How can I do this? By the way, I'm new to macros, but I'm open to using them. Any suggestions?



Selected Answer

Hello Kyle,

The following code is put to work in the attached workbook. Note that it is in XLSM format. When you transfer the code to your own workbook make sure that you install it in the code sheet of the worksheet on which you want the action. It won't work if installed elsewhere. In the attached workbook you will find it in the codesheet of Sheet1.

Option Explicit
Private Enum Nws                ' Worksheet navigation
    ' 04 Sep 2018
    NwsSourceColumn = 5         ' 5 = column E (change as required)
    NwsDisplayColumn = 10       ' 10 = column J (column O would be 15)
End Enum
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 04 Sep 2018
    Const TableName As String = "MyTable"
    Dim Rng As Range
    Set Rng = ActiveSheet.ListObjects(TableName).DataBodyRange
    With Target
        If Not Application.Intersect(.Cells(1), Rng) Is Nothing Then
            ' ===============================
            ' the following 3 lines delete previous displays
            Set Rng = Range(Cells(Rng.Row, NwsDisplayColumn), _
                            Cells(Rng.Row + Rng.Rows.Count - 1, NwsDisplayColumn))
            ' ===============================
            Cells(.Row, NwsDisplayColumn).Value = Cells(.Row, NwsSourceColumn).Value
        End If
    End With
End Sub

Before it can work in your own workbook some adjustments will have to be made.

  1. Set the appropriate value for the enumeration NwsSourceColumn.
    In my example it is column E which Excel identifies by its number 5.
  2. Do the same for enumeration NwsDisplayColumn at the top of the code. In my example it is column J (=10). Change the value to 15 to specify column O.
  3. Look for the Const TableName and assign the actual name of the table in your worksheet to this string.
  4. Finally, I decided that you only want to display one value at a time, the one in the row just clicked. Therefore my code deletes all others. If you don't want that action remove the 3 lines of code I marked, or you might just place an apostrophe at the beginning of each of these 3 lines which would mark it as comment and prevent it from executing.

I have awarded points for your question because it is clear and precise. But it is also a prime candidate for follow-ups from which I ask you to refrain. For onlookers, this is a clean question with a clear answer. Let's not spoil the image. If you (or they) have a related question, post the code in a new thread and ask for the modification you want. Here are some candidates.

First, there is only limited sense in using code to display a hidden value if that value is calculated or retrieved from available data. The task should be carried out by the code and the column not required.

Second, I was tempted to format the display cell with a frame around it and a yellow background and give that same background highlight to the selected row to establish a visual connection. I abstained because if this were done the display cell need not be in the same row. It could be in a permanent location that doesn't need to be deleted when the display changes but might be moved up and down to be on the same page as the click..

The point is that the code provides an infrastructure which identfies the clicked cell (which gives easy access to all the data in that row) and it identifies a range to display a result in. For both ranges, already identified and waiting, it is simple to apply whatever formatting you might wish for without significant extra effort.


Answer the Question

You must create an account to use the forum. Create an Account or Login