How to Change Chart Data as Per Selected Cell in Excel VBA?
Tags: tochangechartdataasp, vbamacros
Published: 2021-09-13 04:31:00

---
title: How to Change Chart Data as Per Selected Cell in Excel VBA?
published: true
date: 2021-09-06 18:12:33 UTC
tags: ToChangeChartDataAsP,VBAMacros
canonical_url: https://geekexcel.com/how-to-change-chart-data-as-per-selected-cell-in-excel-vba/
---

In this article, you will learn  **how to change chart data as per selected cell In Excel VBA**. Let’s see them below!! Get the official version of  **MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

## Step1: Prepare the data in a Sheet as a source for the chart

- Firstly, you have to **create a sample data** from **different regions in a sheet**.
- Now, you need to name it as **source data**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Source-data.png)<figcaption>Source data</figcaption>

## Step 2: Get one region’s data at one time on a different sheet

- You have to **insert a new sheet** and **name it as “Dashboard”**.
- Then, you need to **copy all months** in one column.
- Now, you have to **write one region’s name** adjacent to the **month**.

![](https://geekexcel.com/wp-content/uploads/2021/09/dashboard.png)<figcaption>Dashboard</figcaption>

- After that, you need to pull **data of the region in Cell B1**.
- You have to use the following **formula** given below.

```
 =VLOOKUP(C2,'Source Data'!$A$2:$D$8,MATCH($D$1,'Source Data'!$A$1:$D$1,0)) 
```

- Then, you need to **insert A chart** using this data on the **Dashboard sheet**.
- Now, you have to click on the **chart** icon and select **line chart**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Line-Chart-1024x445.png)<figcaption>Line Chart</figcaption>

## Step 3: Change the region as you select a region name in the specified range

- Firstly, you need to **write all region’s names** in a range,and write them in **range A2:A4**.
- You have to **right-click** on the **Dashboard sheet name**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Click-on-Dashboard-Name.png)<figcaption>Click on Dashboard Name</figcaption>

- Then, you need to click on the **View Code option** to enter directly into **Worksheet Module in VBE** so that we can use the worksheet event.
- Now, you have to write the following **code** given below in the **VB Editor**.

```
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("A2:A4")) Is Nothing Then
  Range("A2:A4").Interior.ColorIndex = xlColorIndexNone 
  Dim region As Variant
  region = Target.value
  On Error GoTo err:
  Select Case region
   Case Is = "Central"
     Range("D1").value = region
   Case Is = "East"
     Range("D1").value = region
   Case Is = "West"
     Range("D1").value = region
   Case Else
     MsgBox "Invalid Option"
End Select
Target.Interior.ColorIndex = 8
End If
err:
End Sub

```

- After that, you need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-14.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-10-1024x184.png)<figcaption>Macro Option</figcaption>

- After that, you need to select a cell in range **A2:A4** , and it’s **value** will be assigned to **D1** and the **data of the chart** will change accordingly.
- Finally, you will receive output as given below.

![](https://geekexcel.com/wp-content/uploads/2021/09/Data-of-the-Chart-1024x525.png)<figcaption>Data of the chart</figcaption>

## A Short Summary

In this tutorial, we guided you on how to **change chart data as per the**  **selected cell In Excel VBA**. Leave your **queries/suggestions** in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

**Keep Reading:**

- **[Creating Quarterly Sales Chart by Clustered Region in Excel 365!!](https://geekexcel.com/creating-quarterly-sales-chart-by-clustered-region-in-excel-365/)**
- **[How to Create a Drop-Down List in Excel 365? -Using Kutools](https://geekexcel.com/how-to-create-a-drop-down-list-in-excel-365-using-kutools/)**
- **[How to Create a Step Chart in Excel using Kutools?](https://geekexcel.com/how-to-create-a-step-chart-in-excel-using-kutools/)**
- **[How to Unhide a Worksheet in Microsoft Excel 365?](https://geekexcel.com/how-to-unhide-a-worksheet-in-microsoft-excel-365/)**
- **[How to Create a Column Chart With Percentage Change in Excel?](https://geekexcel.com/how-to-create-a-column-chart-with-percentage-change-in-excel/)**