VBA store msoThemeColor in variable

JKo_FinanceUse Published at Dev

JKo_FinanceUse

I am looking to store mso theme colors in a variable so that the chart colors and patterns can change dynamically (variable Th in this situation). Here is my current code:

Dim Th As Long
Th = "msoThemeColorAccent" & ActiveCell.Offset(-5, 0)
If ActiveCell = "Realized" Then
    ActiveChart.SeriesCollection(Srs).Select

    With Selection.Format.Fill
        .ForeColor.ObjectThemeColor = Th
        .Solid
    End With
    With Selection.Format.Line
       .ForeColor.ObjectThemeColor = Th
    End With
End If

I'm thinking the problem here is that I am not using the correct Dim categorization. I've seen questions on here on storing RGB as variables (using Dim Long seemed to be the solution) but nothing on msoThemeColors yet. Thanks and let me know if I can provide any other details!

Scott Holtzman

Since msoThemeColorAccent is an enumeration, the below refactored code will work.

Dim lThemeColor As Long
lThemeColor = ActiveCell.Offset(-5, 0) + 4
'msoThemeColor1 enum is 5, 2 is 6, 3 is 7 ... so add 4 to the value 

If ActiveCell = "Realized" Then

    With ActiveChart.SeriesCollection(Srs).Format
        With .Fill
            .ForeColor.ObjectThemeColor = lThemeColor
            .Solid
        End With
        With .Line
           .ForeColor.ObjectThemeColor = lThemeColor
        End With
    End With

End If

Collected from the Internet

Please contact [email protected] to delete if infringement.