Excel中获取图表系列的颜色

分类:代码, 博客 标签:,

Excel中图表一般每个系列都有自己相对应的颜色,有时我们就需要这些系列的颜色来对系列对应的数据表单元格进行着色,以便做出更美观的报告或者展示,这里水文工具集给出一个的示例,示例中包含了相应的VBA源代码,具体应用时可以就具体问题修改代码内部的部分参数。

'================================
' Excel中获取图表系列的颜色
'
' http://cnhup.com
'================================
Sub Get_Series_RGB()
    Dim Cht As Chart
    Dim Srs As Series
    Dim R As Integer
    Dim G As Integer
    Dim B As Integer
    Dim Rng As Range

    Set Cht = ActiveChart

    Worksheets.Add().Name = "RGB"
    Set Rng = Sheets("RGB").Range("A1")

    Rng.Value = "Series"
    Rng.Offset(0, 1).Value = "Red"
    Rng.Offset(0, 2).Value = "Green"
    Rng.Offset(0, 3).Value = "Blue"

    Set Rng = Rng.Offset(1, 0)

    For Each Srs In Cht.SeriesCollection

        GetRGB Srs.Interior.Color, R, G, B

        Rng.Value = Srs.Name
        Rng.Interior.Color = Srs.Interior.Color
        Rng.Offset(0, 1).Value = R
        Rng.Offset(0, 2).Value = G
        Rng.Offset(0, 3).Value = B

        Set Rng = Rng.Offset(1, 0)

    Next Srs

End Sub

Sub GetRGB(RGB As Long, _
     ByRef Red As Integer, _
     ByRef Green As Integer, _
     ByRef Blue As Integer)
    Red = RGB And 255
    Green = RGB \ 256 And 255
    Blue = RGB \ 256 ^ 2 And 255
End Sub


分类:代码, 博客 标签:,

发表评论

You must be logged in to post a comment.