VBA Color point in Chart Excel

Yana Burmistrova
Yana Burmistrova used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
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

Author

Commented:
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?
Top Expert 2016

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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?
Top Expert 2016

Commented:

Author

Commented:
GREAT!!!!!!!!!

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial