Looping through a data chart series to format fill based on cell value

[IanBWiz]

The VBA code below formats the interior colours of a Pie Chart based on entries (1, 2 or 3) in a format column (G15:G29).
What I can't work out is how to start the macro from any initially selected cell in column G (variable starting point) and then exit the macro when it hits the first blank cell.
Can anyone help? Thanks.

Sub FormatChartFillAndFont()
Dim I As Long
Dim FormatCol As Range

Set FormatCol = Range("G15:G29")
With ActiveSheet.ChartObjects("PieChart1").Chart.SeriesCollection(1)
For I = 1 To 15
Select Case FormatCol.Cells(I, 1).Value

Case Is = 1
.Points(I).Interior.Color = RGB(255, 255, 255)
.Points(I).Format.Line.Visible = msoFalse
.DataLabels.Font.Size = 10
.Points(I).DataLabel.Font.Color = vbWhite

Case Is = 2
.Points(I).Interior.Color = RGB(0, 102, 0)
.Points(I).Format.Line.Visible = msoTrue
.DataLabels.Font.Size = 10
.Points(I).DataLabel.Font.Color = vbWhite

Case Is = 3
.Points(I).Interior.Color = RGB(0, 153, 0)
.Points(I).Format.Line.Visible = msoTrue
.DataLabels.Font.Size = 10
.Points(I).DataLabel.Font.Color = vbWhite

End Select
Next I
End With
End Sub

 

[BSALV]

in a normal module

Sub FormatChartFillAndFont()
     Dim I     As Long
     Dim FormatCol As Range

     Set FormatCol = Range("G15:G29")
     With ActiveSheet.ChartObjects("PieChart1").Chart.SeriesCollection(1)
          For I = 1 To 15
               Select Case FormatCol.Cells(I, 1).Value

                    Case Is = 1
                         .Points(I).Interior.Color = RGB(255, 255, 255)
                         .Points(I).Format.Line.Visible = msoFalse
                         .DataLabels.Font.Size = 10
                         .Points(I).DataLabel.Font.Color = vbWhite

                    Case Is = 2
                         .Points(I).Interior.Color = RGB(0, 102, 0)
                         .Points(I).Format.Line.Visible = msoTrue
                         .DataLabels.Font.Size = 10
                         .Points(I).DataLabel.Font.Color = vbWhite

                    Case Is = 3
                         .Points(I).Interior.Color = RGB(0, 153, 0)
                         .Points(I).Format.Line.Visible = msoTrue
                         .DataLabels.Font.Size = 10
                         .Points(I).DataLabel.Font.Color = vbWhite

                    Case "": Exit For                           '<------------------exit with the 1st empty value

               End Select
          Next I
     End With
End Sub

in the sheetmodule

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Not Intersect(Target, Range("G15:G29")) Is Nothing Then FormatChartFillAndFont
End Sub

 

[IanBWiz]

Thanks Rich. This covers the last bit about stopping the macro when hitting a blank but not the first bit about starting it at a variable starting point - the format column can vary in range and start anywhere in the column, not necessarily G15. Any thoughts? Cheers.

 

[BSALV]

Can you describe the problem (in words).
When has the macro to start and who does it knows the startpoint ?
Are the cells in G formulas or constants ?
The problem is too vague.

 

[IanBWiz]

Hi BSALV.
OK, I'll try again. The Pie Chart segment fill colours are controlled (sequentially coloured) by a column of cells (the format column) that contain either a 1, 2 or 3. The first active cell in the format column, and the range of values in the column, can change. So, for example, the first cell containing a format code (1, 2 or 3) could be G13 or it could be G53 or anything else. I would look to get to this first cell by applying the starting code below followed by code that loops and then stops when blank. I have used a Select Case approach but maybe there is a better way of doing this.

Range("G13").Select
Selection.End(xlDown).Select

I hope this helps. Appreciate you looking at this BSALV.