vba - 我可以使用 VBA 进行条件循环以在有条件格式的表格上查找交通灯图标吗

Question

我有一个表格，它已经根据一些带有红绿灯图标的值进行了条件格式化

基本上我希望循环检查以下内容：

1. 如果交通灯是当前月份的绿色，则检查= N
2. 如果交通灯是黄色的，检查 = Y
3. 如果红绿灯是红色的，检查是 Y
4. 如果为空，则检查为 N
5. 如果上个月的灯是红色或黄色，检查 = Y
6. 如果它是空的，检查 = N

到目前为止，这是我所拥有的，但红绿灯条件不起作用：

D

im locate_month As Range
Dim lastmonth As Range
Dim lastquarter As Range
Dim previousmonth As String
Dim Frequency As Range

Dim lrow As Integer
Dim row As Integer

Dim Check As Range

Dim currentmonth1 As String
Set ws = Worksheets("Current KIs")

currentmonth1 = Sheets("Instruction").Range("B6").Value
previousmonth = Sheets("Instruction").Range("B7").Value
previousquarter = Sheets("Instruction").Range("B9").Value
With ws.Range("3:3")
    Set locate_month = .Find(currentmonth1, LookIn:=xlValues)
    Set lastmonth = .Find(previousmonth, LookIn:=xlValues)
    Set lastquarter = .Find(previouquarter, LookIn:=xlValues)
    Set Frequency = .Find("Frequency", LookIn:=xlValues)
    Set Check = .Find("Check (Y/N)", LookIn:=xlValues)
End With

col = locate_month.Column


' start the loop based on condition'

'find the last row of the data set
lrow = ws.Cells(Rows.Count, "B").End(xlUp).row

'loop through all rows
For row = 4 To lrow
    'Check for month values
    If UCase(ws.Cells(row, "B").Value) = "M" Then
        If ws.Cells(row, locate_month.Column) = "" Then
            ws.Cells(row, Check.Column) = "N"
        ElseIf ws.Cells(row, locate_month.Column) = "N/A" Or "n/a" Then
            ws.Cells(row, Check.Column) = "N"
        ElseIf ws.Cells(row, locate_month.Column).FormatCondition(1).Icon = xlIconGreenTrafficLight Then
            ws.Cells(row, Check.Column) = "N"
        ElseIf ws.Cells(row, locate_month.Column) = "G" Then
            ws.Cells(row, Check.Column) = "N"
        Else
            ws.Cells(row, Check.Column) = "Y"
        End If
        'Check previouse month value for change in color
        If ws.Cells(row, lastmonth.Column).FormatCondition(1).Icon = xlIconYellowTrafficLight Then
            ws.Cells(row, Check.Column) = "Y"
        ElseIf ws.Cells(row, lastmonth.Column).FormatCondition(1).Icon = xlIconRedTrafficLight Then
            ws.Cells(row, Check.Column) = "Y"
        ElseIf ws.Cells(row, lastmonth.Column) = "R" Then
         ws.Cells(row, Check.Column) = "Y"
        Else
            ws.Cells(row, Check.Column) = "Y"
         
        End If
   
Next
End Sub

   

标签： vba