多工作表中仅对G列黄色单元格批量设置特定格式（VBA需求）

Hey there! Since you're a CPA who's been figuring out VBA by tweaking recorded macros, I've put together a straightforward script that'll handle your formatting task perfectly. This will go through every sheet in your workbook, target only the yellow-highlighted cells in column G, and apply the exact formatting you need for web import.

Here's the VBA code you can use:

Sub FormatYellowCellsInColumnG()
    Dim ws As Worksheet
    Dim yellowRange As Range
    Dim cell As Range
    
    ' Loop through every worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Set up the range to search: entire column G of the current sheet
        With ws.Columns("G")
            ' Find all cells with yellow fill (using standard yellow RGB value)
            On Error Resume Next ' Avoid error if no yellow cells exist
            Set yellowRange = .SpecialCells(xlCellTypeAllFormatConditions)
            ' If no conditional formatting, check for direct fill color
            If yellowRange Is Nothing Then
                Set yellowRange = .Cells.SpecialCells(xlCellTypeConstants)
                For Each cell In yellowRange
                    ' Check if cell fill is yellow (RGB: 255,255,0)
                    If cell.Interior.Color = RGB(255, 255, 0) Then
                        ' Apply required formatting
                        cell.HorizontalAlignment = xlRight
                        cell.VerticalAlignment = xlBottom
                        cell.NumberFormat = "mm/dd/yyyy"
                        ' Yellow fill stays intact since we don't overwrite it
                    End If
                Next cell
            Else
                ' Handle cells with conditional yellow formatting
                For Each cell In yellowRange
                    If cell.Interior.Color = RGB(255, 255, 0) Then
                        cell.HorizontalAlignment = xlRight
                        cell.VerticalAlignment = xlBottom
                        cell.NumberFormat = "mm/dd/yyyy"
                    End If
                Next cell
            End If
            On Error GoTo 0 ' Reset error handling
        End With
    Next ws
    
    MsgBox "Formatting complete! All yellow cells in column G across sheets are ready for import.", vbInformation
End Sub

Quick breakdown of what this does: #

• Loops through every worksheet: No need to handle each sheet one by one—this covers your entire large workbook automatically.
• Targets yellow cells: It checks both cells with direct yellow fill and those using conditional formatting to make sure we don't miss any.
• Applies your required formatting:
  • Keeps the yellow highlight intact (we never modify the fill color)
  • Sets alignment to bottom-right (xlRight + xlBottom)
  • Applies the custom date format mm/dd/yyyy to ensure web import compatibility

How to use this: #

1. Open your workbook
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer pane > Insert > Module
4. Paste the code above into the new module
5. Press F5 to run the macro, or assign it to a button on your worksheet if you need to reuse it later

This script is built to be easy to tweak if needed—since you're used to modifying recorded macros, you can adjust the column (change "G" to another letter) or RGB color value if your yellow is a different shade.

内容的提问来源于stack exchange，提问作者S. Thomp