Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Color point in Chart Excel

Posted on 2016-09-02
8
Medium Priority
?
29 Views
Last Modified: 2016-10-03
Dear All,

could your please help me with the Excel Color Chart?

I am really Need in your help!

I have a Chart in Excel,
The Chart Shows the Name of the product and two values.I created it withour Pivot Table.

Could you please help me,how can i do it in VBA,I want to choose the one product,double clicked on the product,and the point only of the choosing product will be automatically change the Color in the graph?

is it possible also to add the Name of the choosing product on the upp of the dot ?


I am very appreciate your help!Please help me...
Excel-VBA.xlsx
0
Comment
Question by:Yana Burmistrova
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41781149
Hi,

pls try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set myRng = ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count - 1).Offset(1)

Set SerCol = ActiveSheet.Shapes(1).Chart.SeriesCollection(1)
ActiveSheet.Shapes(1).Chart.ClearToMatchStyle
On Error Resume Next
SerCol.DataLabels.Delete
On Error GoTo 0
If Not Intersect(Target, myRng) Is Nothing Then
    res = 0
    On Error Resume Next ' find corresponding XValue
    res = WorksheetFunction.Match(UsedRange.Cells(Target.Row, 2), SerCol.XValues, 0)
    On Error GoTo 0
    If res <> 0 Then ' if found
        SerCol.Points(res).MarkerBackgroundColor = RGB(255, 0, 0)
        SerCol.Points(res).ApplyDataLabels
        SerCol.Points(res).DataLabel.ShowCategoryName = True
        SerCol.Points(res).DataLabel.ShowValue = False
    End If
End If
End Sub

Open in new window

Regards
Excel-VBAv2.xlsm
0
 

Author Comment

by:Yana Burmistrova
ID: 41781154
everything is great!!!!

Thank you so much!

But is it possible to do ,Show upp of the dot the Name of the product,not the value?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41781156
Corrected code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set myRng = ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count - 1).Offset(1)

Set SerCol = ActiveSheet.Shapes(1).Chart.SeriesCollection(1)
ActiveSheet.Shapes(1).Chart.ClearToMatchStyle
On Error Resume Next
SerCol.DataLabels.Delete
On Error GoTo 0
If Not Intersect(Target, myRng) Is Nothing Then
    res = 0
    On Error Resume Next ' find corresponding XValue
    res = WorksheetFunction.Match(UsedRange.Cells(Target.Row, 2), SerCol.XValues, 0)
    On Error GoTo 0
    If res <> 0 Then ' if found
        SerCol.Points(res).MarkerBackgroundColor = RGB(255, 0, 0)
        SerCol.Points(res).HasDataLabel = True
        SerCol.Points(res).DataLabel.Text = UsedRange.Cells(Target.Row, 1)
    End If
End If
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Yana Burmistrova
ID: 41781161
For example, when we choose the product,in the graph the Color Change and Shows the value,is it possible to Show the Name of the product,not the value?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41781165
0
 

Author Comment

by:Yana Burmistrova
ID: 41781170
GREAT!!!!!!!!!

THANK YOU SO MUCH!!!!!!!!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question