Excel技术需求：填充首单元格后自动将B2、C2、D2设为星号

Hey there! Let's solve this Excel automation task for your directory sheet. I've got two solid methods for you—one using simple formulas (no macros required) and another with VBA for more control, depending on your needs.

Method 1: Use IF Formulas (No VBA Needed) #

This is the easiest approach if you only need basic automation and don't mind that manual edits will replace the formula (since you said manual modifications are allowed).

• Select cell B2 and enter this formula:

  =IF(A2<>"", "*", "")
  

• Click and drag the fill handle (the small square at the bottom-right of B2) over to C2 and D2 to copy the formula.
• How it works: When you type any text or number into A2, B2/C2/D2 will automatically show *. If you want to edit any of these cells later, just type directly into them—this will overwrite the formula, and that cell won't sync with A2 anymore (perfect for one-off manual changes).

Method 2: VBA Macro (Persistent Automation + Manual Edit Flexibility) #

If you want the cells to automatically reset to * when A2 is updated, but still allow manual edits that won't get overwritten unless A2 changes again, VBA is the way to go.

Step-by-Step Setup: #

1. Open your Excel file and press Alt + F11 to open the VBA Editor.
2. In the left Project Explorer, find your directory sheet (e.g., Sheet1), then double-click it to open the code window.
3. Paste this code into the window:

   Private Sub Worksheet_Change(ByVal Target As Range)
       ' Check if the changed cell is A2
       If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
           ' If A2 has content, fill empty cells in B2:D2 with *
           If Me.Range("A2").Value <> "" Then
               Dim cell As Range
               For Each cell In Me.Range("B2:D2")
                   If cell.Value = "" Then
                       cell.Value = "*"
                   End If
               Next cell
           ' If A2 is cleared, empty B2:D2
           Else
               Me.Range("B2:D2").ClearContents
           End If
       End If
   End Sub
   

4. Save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep the macro active.

What This Does: #

• When you enter content into A2, any empty cells in B2/C2/D2 will automatically fill with *—cells you've already manually edited will stay as-is.
• If you clear A2, B2/C2/D2 will all be emptied.
• If you update A2 later, it'll only fill the cells that are still empty with *—your manual edits remain intact.

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