彩色條形圖；不確定我收集的范圍是否正確？-有解無憂

我正在努力應用我在不同作業簿中用于同一程序的先前代碼。

這個程序是我有條件地格式化了一組資訊（現在在不同的作業表上）以根據是從下拉串列中選擇“實驗室”還是“辦公室”來改變顏色。

然后我想要（我相信這段代碼應該做什么，但我不相信我已經正確地鏈接了該系列）與資訊相關的圖表也可以將相關資料點更改為該顏色，突出顯示在這一層你有選擇“辦公室”或“實驗室”。

我使用的代碼從這里開始：

Sub CellColorsToChart()
    Dim xChart As Chart
    Dim I As Long, J As Long
    Dim xRowsOrCols As Long, xSCount As Long
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    
    Set xChart = ActiveSheet.ChartObjects("Net Internal Area").Chart.Refresh
    If xChart Is Nothing Then Exit Sub
    
    xSCount = xChart.SeriesCollection.Count
    
    For I = 1 To xSCount
        J = 1
        With xChart.SeriesCollection(I)
            Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1))
            If xSCount > 4 Then
                xRowsOrCols = xRg.Columns.Count
            Else
                xRowsOrCols = xRg.Rows.Count
            End If
            For Each xCell In xRg
                .Points(J).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(xCell.DisplayFormat.Interior.ColorIndex)
                .Points(J).Format.Line.ForeColor.RGB = ThisWorkbook.Colors(xCell.DisplayFormat.Interior.ColorIndex)
                J = J   1
            Next
        End With
    Next
End Sub

檔案可以在這里下載：https : //wetransfer.com/downloads/fbdb338026e7c42cc08193536270cdfc20211115102313/07937d

任何關于如何更好地理解和閱讀本文的幫助或提示都會很棒。

最好的杰克

uj5u.com熱心網友回復：

Refresh從這條線中洗掉Set xChart = ActiveSheet.ChartObjects("Net Internal Area").Chart.Refresh并在之后重繪圖表If xChart Is Nothing Then Exit Sub

Option Explicit

Sub CellColorsToChart()

    Dim xChart As Chart
    Dim I As Long, J As Long, ix As Long
    Dim xSCount As Long
    Dim xRg As Range, xCell As Range
    
    Set xChart = ActiveSheet.ChartObjects("Net Internal Area").Chart
    If xChart Is Nothing Then Exit Sub
    xChart.Refresh
    xSCount = xChart.SeriesCollection.Count
    
    For I = 1 To xSCount
        With xChart.SeriesCollection(I)
            J = 1
            Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1))
          
            For Each xCell In xRg
                ix = xCell.DisplayFormat.Interior.ColorIndex
                If ix >= 1 Then
                   .Points(J).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(ix)
                   .Points(J).Format.Line.ForeColor.RGB = ThisWorkbook.Colors(ix)
                End If
                J = J   1
            Next
        End With
    Next
End Sub

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/357737.html

標籤：擅长 vba

上一篇：使用標準查找最接近的3個值

下一篇：如何在Excel中找到多個專案的最大總和