Excel 2007 VBA Cell.interior交替基于连续单元计算到目标-Java 学习之路

我会尽我所能使这简短而甜蜜 . 我有一个excel工作表，需要对单元格求和，直到达到目标 . 那些细胞需要内部颜色集 . 然后重置计算然后继续下行，直到下一轮单元格等于目标 . 内部应设置为此组的不同颜色 . 这将在整个范围内交替 . 我有大约90％，但第二种颜色只突出集合的开始，然后继续第一种颜色 . 提前感谢您的任何指示

我有：

Sub calctarget()
    Dim x As Variant
    Dim xsub As Variant
    Dim total  As Double
    Dim y As Variant
    Dim target As Double
    Dim RunTotal As Double

    target = 44500

    If RunTotal < target Then y = 0
    RunTotal = 0
    For Each y In Range("a1:a15")
        On Error Resume Next
        RunTotal = RunTotal + y.Value
        If RunTotal < target Then
            y.Interior.ColorIndex = 4
        End If
        If RunTotal > target Then
            RunTotal = RunTotal - y
            If y.Offset(-1, 0).Interior.ColorIndex = 4 Then
                RunTotal = 0
                If RunTotal = 0 Then y.Interior.ColorIndex = 5
                RunTotal = RunTotal + y
            End If
        End If
    Next y
End Sub

1 回答

这可能是您需要的（每次达到目标时它会交替颜色，如果有的话，它会向前移动 balancer ）：

Sub calctarget()
    Dim c As Range
    Dim target As Double
    Dim runTotal As Double
    Dim currentColor As Long

    target = 44500
    currentColor = 4

    For Each c In Range("a1:a15")
        If IsNumeric(c) Then
            runTotal = runTotal + c
        End If
        If runTotal >= target Then 'Target reached
            currentColor = IIf(currentColor = 4, 5, 4) 'alternate colors (4 and 5)
            runTotal = runTotal - target 'maybe you want to start from 0 again?
        End If
        c.Interior.ColorIndex = currentColor
    Next c
End Sub

回复于 2024-04-19T11:31:24+08:00

Excel 2007 VBA Cell.interior交替基于连续单元计算到目标

1 回答

相关问题