VBA 根据内容文件类型格式化单元格填充颜色

Question

我正在尝试读取 Excel 列中的文档列表，并根据文件类型更改单元格的颜色。但我做不到。有什么解决办法吗？

Public Sub Master()

       Dim TdCel As Range, FCell As Range

       Set TdCel = Range("A1:A25")

       For Each FCell In TdCel

          If FCell.Text = "*.pdf" Then
                FCell.Interior.ColorIndex = 10
            ElseIf FCell.Value = "*.*.doc" Then
                FCell.Interior.ColorIndex = 9
            ElseIf FCell.Value = "*.jpg" Then
                FCell.Interior.ColorIndex = 8
            Else
                FCell.Interior.Color = vbWhite
          End If
       Next

    End Sub

Answer 1

一些改进:

1. 定义您工作的工作表(在 Set wS = ... 行中更改工作表名称
2. 使用With
3. 对小写单元格的值 (LCase()) 使用 Select Case 和 Like

工作解决方案:

Public Sub Master_JoaoTS()
    Dim wS As Worksheet
    Dim TdCel As Range, FCell As Range, CellVal As String
    Set wS = ThisWorkbook.Sheets("Sheet's Name")
    Set TdCel = wS.Range("A1:A25")

    For Each FCell In TdCel
        With FCell
            CellVal = LCase(.Value)
            With .Interior
                Select Case True
                    Case CellVal Like "*.pdf"
                        .ColorIndex = 10
                    Case CellVal Like "*.doc*"
                        .ColorIndex = 9
                    Case CellVal Like "*.jpg"
                        .ColorIndex = 8
                    Case Else
                        .Pattern = xlNone
                End Select
            End With '.Interior
        End With 'FCell
    Next FCell
End Sub