vba Excel / VB - 如何遍历每一行/列并根据值进行格式化？

Excel / VB - How do I loop through each row/column and do formatting based on the value?

提问by Johnny 5

Here's what I need to do:

这是我需要做的：

1) Loop through every cell in a worksheet 2) Make formatting changes (bold, etc) to fields relative to each field based on the value

1) 遍历工作表中的每个单元格 2) 根据值对与每个字段相关的字段进行格式更改（粗体等）

What I mean is that if a field has a value of "foo", I want to make the field that is (-1, -3) from it bold, etc. I tried to do this with the following script with no luck.

我的意思是，如果一个字段的值是“foo”，我想将它的 (-1, -3) 字段设为粗体，等等。我尝试使用以下脚本执行此操作，但没有成功。

Thanks Johnny

谢谢约翰尼

Pseudo Code to Explain:

伪代码解释：

For Each Cell in WorkSheet
    If Value of Cell is 'Subtotal'
        Make the cell 2 cells to the left and 1 cell up from here bold and underlined
    End If
End ForEach

The Failed Macro (I don't really know VB at all):

失败的宏（我根本不了解 VB）：

Sub Macro2()
'
'
'
Dim rnArea As Range
Dim rnCell As Range

Set rnArea = Range("J1:J2000")

    For Each rnCell In rnArea
        With rnCell
            If Not IsError(rnCell.Value) Then
                Select Case .Value
                    Case "000 Total"
                        ActiveCell.Offset(-1, -3).Select
                        ActiveCell.Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingleAccounting
                End Select
            End If
        End With
    Next
End Sub

回答by marg

Option Explicit

Private Sub macro2()
    Dim rnArea As Range
    Dim rnCell As Range

    ' you might need to change the range to the cells/column you want to format e. g. "G1:G2000" '
    Set rnArea = Range("J1:J2000")

    For Each rnCell In rnArea
        With rnCell
            If isBold(.Offset(1, 3).Value) Then
                .Font.Bold = True
            End If
            If isUnderlined(.Offset(1, 3).Value) Then
                'maybe you want this: .Font.Underline = xlUnderlineStyleSingle '
                .Font.Underline = xlUnderlineStyleSingleAccounting
            End If
        End With
    Next
End Sub

Private Function isBold(cellValue As Variant) As Boolean
    Dim myList() As Variant
    Dim listCount As Integer
    Dim i As Integer

    myList = Array("Totals", "FooTotal", "SpamTotal")
    listCount = 3

    isBold = False
    For i = 0 To listCount - 1
        If cellValue = myList(i) Then
            isBold = True
            Exit Function
        End If
    Next i
End Function

Private Function isUnderlined(cellValue As Variant) As Boolean
    Dim myList() As Variant
    Dim listCount As Integer
    Dim i As Integer

    myList = Array("FooTotal", "SpamTotal")
    listCount = 2

    isUnderlined = False
    For i = 0 To listCount - 1
        If cellValue = myList(i) Then
            isUnderlined = True
            Exit Function
        End If
    Next i
End Function

I added two functions but it should have also worked with an extensive if / else if / else.

我添加了两个函数，但它也应该与广泛的 if / else if / else 一起工作。

回答by TerrorAustralis

Based on the comments on the solution above, i think this might be helpful

基于对上述解决方案的评论，我认为这可能会有所帮助

Sub FormatSpecialCells()
    Dim SearchRange As Range
    Dim CriteriaRange As Range

    Set SearchRange = Range("A2:A24")
    Set CriteriaRange = Range("C2:C5")

    Dim Cell As Range

    For Each Cell In SearchRange
     TryMatchValue Cell, CriteriaRange
    Next


End Sub

Private Sub TryMatchValue(CellToTest As Range, CellsToSearch As Range)
    Dim Cell As Range

    For Each Cell In CellsToSearch
        If Cell.Value = CellToTest.Value Then
            Cell.Copy
            CellToTest.PasteSpecial xlPasteFormats, xlPasteSpecialOperationNone, False, False
        End If
    Next
End Sub

This does not fully accomplish your goal. What it does is it searches a specified list of cells, and it matches them against a seperate list of cells. If it matches the values, it takes the FORMAT of the second list of cells and applies it to the cell it matched in the first list of cells. You can modify this by changing the TryMatchValue function so that instead of matching the CellToTest, it pastes the format onto another cell which is 2 across and one up.

这并不能完全实现您的目标。它的作用是搜索指定的单元格列表，并将它们与单独的单元格列表进行匹配。如果它匹配这些值，它将采用第二个单元格列表的 FORMAT 并将其应用于它在第一个单元格列表中匹配的单元格。您可以通过更改 TryMatchValue 函数来修改它，以便它不匹配 CellToTest，而是将格式粘贴到另一个跨 2 和 1 的单元格上。

This has the advantage that, if you want to add more values and different formats, you only need to go to your excel sheet and add more values. Also you only need to change the format on that value.

这样做的好处是，如果要添加更多值和不同格式，只需转到 Excel 表并添加更多值。此外，您只需要更改该值的格式。

An example would be...

一个例子是...

Have the cells you are searching in A1:D1000 Have these values in cells E2:E6... Subtotal (which is bold and underlined) Total (which is bold, underlined and italic) Net (which is bold underlined and Red) etc...

让您在 A1:D1000 中搜索的单元格在单元格 E2:E6 中包含这些值...小计（粗体和下划线）总计（粗体、下划线和斜体）净（粗体下划线和红色）等。 ..

then when it hits Subtotal, it will change the cell to be bold and underlined. When it hits Total it will change the cell to be bold underlined and italic etc etc...

然后当它点击小计时，它会将单元格更改为粗体和下划线。当它点击 Total 时，它会将单元格更改为粗体下划线和斜体等......

hope this helps

希望这可以帮助

回答by Matthew Sposato

Would the conditional formattingfunctionality in excel give you what you need without having to write a macro?

excel 中的条件格式功能是否可以在无需编写宏的情况下为您提供所需的内容？