Excel VBA实现满足单元格背景色条件时在S列自动填入"YES"/"NO"的需求及代码咨询

Let's break down what's off with your current code and refine it to do exactly what you need:

Issues in the Original Code #

• Only targets the active cell's row: Your code only checks the row where your active cell is, so you can't run it on multiple rows without manual repetition.
• Missing "NO" assignment: If there are no green cells in the row, the S column cell stays unchanged—it won't automatically set to "NO".
• Incorrect cell reference: ActiveCell.Column(18) isn't the right way to target column S (column 18) of the current row. You should use Cells(cell.Row, 18) instead.
• Redundant assignments: If a row has multiple green cells, your code will write "YES" to column S multiple times, which is unnecessary.

Optimized Code Options #

Option 1: Check a Single Selected Row

Use this if you want to run the check on just one row at a time (select any cell in the target row first):

Sub CheckSingleRowForGreen()
    Dim targetRow As Range
    Dim cell As Range
    Dim hasGreen As Boolean
    
    ' Set the target row to the row of the active cell
    Set targetRow = ActiveCell.EntireRow
    
    ' Initialize flag to False (no green cells found yet)
    hasGreen = False
    
    ' Loop through cells in the row (adjust columns if you don't need to check EVERY cell)
    For Each cell In targetRow.Cells
        ' Stop checking early once we find a green cell
        If cell.Interior.Color = vbGreen Then
            hasGreen = True
            Exit For ' No need to check remaining cells
        End If
    Next cell
    
    ' Update column S (18) based on the flag
    If hasGreen Then
        Cells(targetRow.Row, 18).Value = "YES"
    Else
        Cells(targetRow.Row, 18).Value = "NO"
    End If
End Sub

Option 2: Check All Rows in Your Data Range

This is better if you have a table of data and want to check every row at once (adjust the dataRange to match your actual data):

Sub CheckAllRowsForGreen()
    Dim dataRange As Range
    Dim targetRow As Range
    Dim cell As Range
    Dim hasGreen As Boolean
    
    ' Define your data range (example: rows 2 to 100, columns A to R)
    ' Adjust this to match where your actual data lives
    Set dataRange = ThisWorkbook.Sheets("Sheet1").Range("A2:R100")
    
    ' Loop through each row in the data range
    For Each targetRow In dataRange.Rows
        hasGreen = False
        
        ' Check each cell in the row
        For Each cell In targetRow.Cells
            If cell.Interior.Color = vbGreen Then
                hasGreen = True
                Exit For
            End If
        Next cell
        
        ' Update column S for the current row
        If hasGreen Then
            Cells(targetRow.Row, 18).Value = "YES"
        Else
            Cells(targetRow.Row, 18).Value = "NO"
        End If
    Next targetRow
End Sub

Key Improvements #

• Early exit: Once a green cell is found in a row, we stop checking the rest of the cells to save time.
• Default "NO" value: Every row gets either "YES" or "NO" explicitly, no leftover values.
• Flexibility: Choose between single-row or bulk processing based on your needs.
• Clear references: Targets column S correctly using row numbers.

How to Use #

1. Open your Excel workbook and press Alt + F11 to open the VBA editor.
2. Insert a new module (right-click your workbook in the Project Explorer > Insert > Module).
3. Paste the code option that fits your workflow.
4. Adjust the dataRange in Option 2 to match your actual data area.
5. Run the macro (press F5 in the editor, or assign it to a button in Excel for easier access).

内容的提问来源于stack exchange，提问作者LDX