Te anexo la macro actualizada.
Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rngSeries As Range
Dim rngSerBeg As Range
Dim rngSerEnd As Range
Dim i As Integer
' on selecting a cell in the workbook this procedure will chart
' series for the selected Item.
' Items must be formatted in Bold and with
' a grey background (interior.colorIndex = 15) - for chtDim
' or a blue background (interior.colorIndex = 37) - for cht
' selecting other cells hides both charts
If Target.Font.Bold = False And Target.Interior.ColorIndex = 19 Then
' hide chMeasure, set the series for chtDim and show chtDim
' first, hide chtMeasure
Sh.ChartObjects("chtMeasure").Visible = False
Sh.ChartObjects("chtColumn").Visible = False
'Set rngSerBeg = Target.Offset(0, 1)
'Set rngSerEnd = Target.Offset(0, 9) 'rngSerBeg.End(xlToRight)
'Set rngSeries = Range(rngSerBeg, rngSerEnd)
[C9] = Cells(Target.Row, "M")
[C10] = Cells(Target.Row, "L")
Sh.ChartObjects("chtDim").Activate
ActiveChart.SetSourceData Source:=Range("C7:K10,C" & Target.Row & ":K" & Target.Row)
ActiveChart.SeriesCollection(1).XValues = "='PRODUCTOS'!$C$7:$K$8"
ActiveChart.Shapes("subH").TextFrame.Characters.Text = Target.Value
' now call the procedure to set the chart series (see below)
' this takes as arguments (the worksheet, chart name, series range, currrent cell, series number)
' it sets the chart series to the given range and unhides the chart
'Call SetChartSeries(Sh, "chtDim", rngSeries, Target, 1)
' finally, move the chart to a place in the worksheet
' relative to the current cell
Sh.Shapes("chtDim").IncrementTop (Target.Offset(1, 1).Top - Sh.Shapes("chtDim").TopLeftCell.Top)
Application.EnableEvents = False
Target.Select
Application.EnableEvents = True
ElseIf Target.Font.Bold = True And Target.Interior.ColorIndex = 37 Then
' hide chtDim, set the series for chtMeasure and show chMeasure
' first, hide chtDim
Sh.ChartObjects("chtDim").Visible = False
Sh.ChartObjects("chtColumn").Visible = False
' chtMeasure has three series, so we loop through each one
For i = 1 To 3
'set the series range to be charted
Set rngSerBeg = Target.Offset(i, 1)
Set rngSerEnd = Target.Offset(0, 9) 'rngSerBeg2.End(xlToRight)
Set rngSeries = Range(rngSerBeg, rngSerEnd)
Next
Call SetChartSeries(Sh, "chtMeasure", rngSeries, Target, 1)
' reposition the chart
Sh.Shapes("chtMeasure").IncrementTop (Target.Offset(4, 1).Top - Sh.Shapes("chtMeasure").TopLeftCell.Top)
' column header selected
If Target.Font.Bold = True And Target.Interior.ColorIndex = 40 Then
' hide chtDim, set the series for chtMeasure and show chMeasure
' first, hide chtDim
Sh.ChartObjects("chtDim").Visible = False
Sh.ChartObjects("chtMeasure").Visible = False
' chtColumn has one series
' set the series range to be charted
Set rngSerBeg = Target.Offset(1, 0)
Set rngSerEnd = Cells(65536, Target.Column).End(xlUp)
Set rngSeries = Range(rngSerBeg, rngSerEnd)
' set the chart series
Call SetChartSeries(Sh, "chtMeasure", rngSeries, Target, i)
Sh.ChartObjects("chtColumn").Chart.ChartTitle.Text = Target
' reposition the chart
Sh.Shapes("chtColumn").IncrementLeft (Target.Offset(1, 1).Left - Sh.Shapes("chtColumn").TopLeftCell.Left)
Else
' if a cell is selected that does not contain an Item to be
' charted, hide both charts
On Error Resume Next
Sh.ChartObjects("chtDim").Visible = False
Sh.ChartObjects("chtMeasure").Visible = False
Sh.ChartObjects("chtColumn").Visible = False
End If
End If
End Sub
' : )
'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias
' : )