时间戳

首页 > 解决方案 > 双 for 循环遍历列 BM 和行，以获取行总数并更改列标题

excel - 双 for 循环遍历列 BM 和行，以获取行总数并更改列标题

问题描述

如果行总计> 130,000，我正在尝试将单元格行标签的字体颜色更改为红色。分配的第一部分需要更改每个单元格中字体的颜色，其值超过 12000 - 我现在拥有的代码正确地为单元格着色，但没有将相应的单元格变为红色（对应于行的 col A 中的单元格它正在迭代以获得总数）。任何帮助将不胜感激，我是 Excel VBA 的新手！

这是我目前的代码：

Option Explicit
Sub Question5()
Dim rng As Range
Dim cell As Range
Dim total As Integer
Dim i As Integer
Set rng = Range("B2", "M41")
For Each cell In rng
    If cell.Value >= 12000 Then _
    cell.Font.ColorIndex = 5
    Next cell
Dim monthlysum As Integer
For Each Row In Range("B2", "M41")
Set monthlysum = 0
    For Each cell In Row
    monthlysum = cell.Value + monthlysum
    Next cell
    If monthlysum > 130000 Then _
    Range("A" & rowNum).Select
    Range("A" & rowNum).Font.Color = vbRed
Next Row
End Sub

标签： excelvba

解决方案

试试这个 - 修复内联注释

Option Explicit

Sub Question5()
    Dim rng As Range, Row As Range, cell as Range
    Dim monthlySum as Long 'use Long not Integer

    For Each Row In ActiveSheet.Range("B2:M41").Rows 'need to specify .Rows otherwise defaults to .Cells
        monthlySum = 0
        For Each cell in Row.Cells
            with cell
               if .Value >= 12000 Then cell.Font.ColorIndex = 5
               monthlySum = monthlySum + .Value
            End With
        Next cell
        if monthlySum > 130000 Then Row.Entirerow.cells(1).Font.color=vbRed 
    Next Row
End Sub

推荐阅读