VBA FOR 用于验证区域中单元格的颜色

Question

我正在做一个宏来检查和验证颜色索引的所有单元格。 如果我的代码有错误，请帮助我。非常感谢。

    Sub Validate()
Dim rng As Range: Set rng = Application.Range("CATALOG!B2:F98")
Dim cel As Range
Dim i As Boolean
i = False
For Each cel In rng
With cel
    If .Interior.ColorIndex = 6 Then
        If .EntireRow.Hidden = False Then
            i = True
        End If
    End If
   End With
   Next cel
 If i = True Then
  MsgBox "yellow"
Else
  MsgBox "none"
End If
End Sub

Answer 1

目前还不清楚你在问什么，但有两种方法：

1）首先，当您要检查范围内的每个单元格是否具有.Interior.ColorIndex = 6时。然后，您的代码应如下所示：

Sub Validate()
Dim rng As Range: Set rng = Application.Range("CATALOG!B2:F98")
Dim cel As Range
Dim i As Boolean
i = True
For Each cel In rng.Cells
    If Not cel.Interior.ColorIndex = 6 Then
        i = False
        Exit For 'we found cell with different color, so we can exit loop
    End If
Next cel
If i = True Then
    MsgBox "yellow"
Else
    MsgBox "none"
End If
End Sub

2）您要检查是否有任何单元格具有.Interior.ColorIndex = 6。然后它应该看起来像：

Sub Validate()
Dim rng As Range: Set rng = Application.Range("CATALOG!B2:F98")
Dim cel As Range
Dim i As Boolean
i = False
For Each cel In rng.Cells
    If cel.Interior.ColorIndex = 6 Then
        i = True
        Exit For 'we found cell with the color, so we can exit loop
    End If
Next cel
If i = True Then
    MsgBox "yellow"
Else
    MsgBox "none"
End If
End Sub

Answer 2

在我设置的内容中，您正在尝试遍历所有非隐藏单元格，看看它们是否具有黄色背景颜色，如果它们是黄色的，则需要根据输入进行更改。

Sub Validate()
Dim rng As Range: Set rng = Application.Range("CATALOG!B1:F98")
Dim cel As Range
For Each cel In rng
    With cel
        If .Interior.ColorIndex = 6 Then
            If .EntireRow.Hidden = False Then
                .Interior.ColorIndex = InputBox("please input new colorIndex", "SetColorIndex")
            End If
        End If
   End With
Next cel
End Sub