为什么这个 IF/ELSE 不一致?

Question

我需要重新格式化 C 列中包含字符串"No Action"的行

我使用这个：

Dim ActionRow as Long

For ActionRow = 2 To 50
If .Cells(ActionRow, 3).Value = "No Action" Then
.Range("A" & ActionRow & ":AB" & ActionRow).Font.Italic = True
.Range("A" & ActionRow & ":AB" & ActionRow).Font.Color = 8421504
End If
Next ActionRow

它不仅定位 C 列中带有"无操作"的行，还定位每一行，或者不一致地跟踪它。如何告诉它只在C列(3(中找到字符串"无操作"，然后执行格式设置？

其余代码：

Option Explicit
Sub Main()
Dim Wb As Workbook
Dim Data, Last, BU7, Lvl7
Dim sourcerow As Long, sourcecol As Long, destrow As Long, destcol As Long
Dim rngDest As Range
Dim ActionRow As Long

'Refer to the template
Set Wb = Workbooks("Book1.xlsx")
'Refer to the destination cell
Set rngDest = Wb.Sheets("Sheet1").Range("A2")
'Read in all data
With ThisWorkbook.Sheets("Data_")
Data = .Range("Ab2", .Range("A" & Rows.Count).End(xlUp))
End With
Wb.Activate
Application.ScreenUpdating = False
'Process the data
For sourcerow = 1 To UBound(Data, 1)
'Manager changes?
If Data(sourcerow, 15) <> Last Then
'Skip the first
If sourcerow > 1 Then
'Scroll into the view
rngDest.Select
'Save a copy
Wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
ValidFileName("10.08.18" & " - " & BU7 & " - " & Lvl7 & " - " & Last & ".xlsx")
End If
'Clear the employees
ActiveSheet.Range("A2:AB" & ActiveSheet.Columns.Count + 1).ClearContents
'Remember this manager
Last = Data(sourcerow, 15)
BU7 = Data(sourcerow, 18)
Lvl7 = Data(sourcerow, 25)
'Start the next round
destcol = 0
End If
'Write the employee data into the template
destrow = 0
For sourcecol = 1 To UBound(Data, 2)
If sourcecol = 1 Then
rngDest.Offset(destcol, destrow) = CStr(Format(Data(sourcerow, sourcecol), "000000000"))
Else
rngDest.Offset(destcol, destrow) = Data(sourcerow, sourcecol)
End If
destrow = destrow + 1
Next
'Next column
destcol = destcol + 1
Next
With Wb.Worksheets("Sheet1")
For ActionRow = 2 To 50
If .Cells(ActionRow, 3).Value = "No Action" Then
.Range("A" & ActionRow & ":AB" & ActionRow).Font.Italic = True
.Range("A" & ActionRow & ":AB" & ActionRow).Font.Color = 8421504
End If
Next ActionRow
.Columns("A:ab").Sort key1:=Range("c2"), _
order1:=xlAscending, Header:=xlYes
.Columns("A:A").NumberFormat = "000000000"

End With
End Sub

Answer 1

下面怎么样，下面的代码将使用值"无操作"自动筛选 C 列，而不是使用 For 循环，然后它将设置为斜体并为从 A 列到 AB 的每个可见行着色：

With WB.Worksheets("Sheet1")
.Cells.AutoFilter 'add AutoFilter
.Range("$A$1:$AB$50").AutoFilter Field:=3, Criteria1:="No Action"
.Range("$A$2:$AB$50").SpecialCells(xlCellTypeVisible).Font.Italic = True
.Range("$A$2:$AB$50").SpecialCells(xlCellTypeVisible).Font.Color = 8421504
.Cells.AutoFilter 'remove AutoFilter
End With

更新：

若要在使用自动筛选之前检查 C 列上的值"无操作"，可以执行以下操作：

With WB.Worksheets("Sheet1")
Set FoundNoAction = .Range("C:C").Find(What:="No Action", Lookat:=xlWhole)
If Not FoundNoAction Is Nothing Then
.Cells.AutoFilter 'add AutoFilter
.Range("$A$1:$AB$50").AutoFilter Field:=3, Criteria1:="No Action"
.Range("$A$2:$AB$50").SpecialCells(xlCellTypeVisible).Font.Italic = True
.Range("$A$2:$AB$50").SpecialCells(xlCellTypeVisible).Font.Color = 8421504
.Cells.AutoFilter 'remove AutoFilter
End If
End With

Answer 2

我将像这样使用 .findnext：

With sheets(youractionsheet).Range(“A2:AB” & sheets(youractionsheet).cells(rows.count,3).end(xlup).row)
Set myaction = .cells.find(what:=“No Action”)
If not myaction is nothing then 
Do until myaction is nothing
myaction.font.italic = true
myaction.font.color = 8421504
Set myaction = .findnext(myaction)
Loop
End if
End with