Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Color point in Chart Excel

Posted on 2016-09-02
8
Medium Priority
?
33 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
  • 3
  • 3
8 Comments
 
LVL 53

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 53

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 53

Expert Comment

by:Rgonzo1971
ID: 41781165
0
 

Author Comment

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

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

971 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