Hi,
I have test script statuses of scripts for two distinct Software Modules in an excel sheets. Each module's test scripts have following statuses:
'1. Not Started'
'2. In Progress'
'3. Ready for Audit'
'4. In Audit'
'5. In rework'
'6. Complete'
'7. Removed'
I've numbered them in sequence of script development life cycle. The main reason is because excel considers the sequence alphabetically. So, both modules have a pie-chart metric showing what is the '%' of each script status in the total inventory. I'm using the below macro.
Sub ColorByCategoryLabel()
Dim oChart As ChartObject
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range
Set rPatterns = ActiveWorkbook.Worksheets("DV Progress").Range("A159:A165")
' Loop through all charts on main page
For Each oCht In ActiveWorkbook.Worksheets("DV Progress").ChartObjects
With oCht.Chart.SeriesCollection(1)
vCategories = .XValues
' Loop through all categories of chart,
' find and set color of matching category
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Next
End With
Next
End Sub
It was working fine, but now I notice some colors not correct in the chart. So, I opened the vba editor (Ctrl+F11) and selected the macro and clicked on the 'play' button and got the Run-time error '91': Object variable or With block variable not set
When I click on Debug, following line is highlighted:
.Points(iCategory).Interior.ColorIndex = rCategory.Interior.ColorIndex
Any clues..? Thanks !!
Bookmarks