Excel表格：如何创建连续三个YES识别公式并实现单元格自动变色及行内其余单元格锁定

Got it, let's break down how to implement your three requirements for tracking customer mastery criteria in Excel. We'll use a mix of conditional formatting for visual cues and VBA for cell locking functionality.

Step 1: Identify Rows with Three Consecutive "YES" Values #

First, we'll set up a conditional formatting rule to detect when cells like E8, F8, G8 are all "YES":

1. Select the entire range of cells where your YES/NO dropdowns live (e.g., columns E to G).
2. Go to the Home tab → Conditional Formatting → New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter this formula (note the relative references—they’ll adapt to each row automatically):

   =AND(E1="YES",F1="YES",G1="YES")
   

5. Click Format to set up your color coding (we’ll refine this in Step 2).

Step 2: Apply Color Coding for Mastery Criteria #

If you want each of the three "YES" cells to have a distinct color (blue, red, green), you’ll need three separate conditional formatting rules:

• For Column E: Repeat Step 1, but select only column E. Use the same formula =AND($E1="YES",$F1="YES",$G1="YES") (the $ locks the column references), then set fill color to blue.
• For Column F: Select column F, use the same formula, set fill color to red.
• For Column G: Select column G, use the same formula, set fill color to green.

If you prefer all three cells to use the same color, just use one rule applied to E:G and pick a single fill color in the format settings.

Step 3: Lock Non-Target Cells with VBA #

Conditional formatting can’t lock cells, so we’ll use a worksheet change event to automate this. Here’s how:

1. First, prepare your worksheet for protection:
   • Select the entire sheet (click the top-left corner between row 1 and column A).
   • Right-click → Format Cells → Protection tab → Uncheck "Locked" → Click OK. This unlocks all cells by default, so we can lock only the ones we need later.
2. Open the VBA Editor (press Alt + F11).
3. In the Project Explorer (left pane), double-click the worksheet where your table lives.
4. Paste this code into the code window:

   Private Sub Worksheet_Change(ByVal Target As Range)
       ' Define the columns we're checking (E=5, F=6, G=7)
       Dim checkRange As Range
       Set checkRange = Me.Range("E:G")
       
       ' Only run if the edited cell is in our YES/NO columns
       If Not Intersect(Target, checkRange) Is Nothing Then
           Dim targetRow As Integer
           targetRow = Target.Row
           
           ' Check if all three cells in the row are "YES"
           If Me.Cells(targetRow, 5).Value = "YES" And _
              Me.Cells(targetRow, 6).Value = "YES" And _
              Me.Cells(targetRow, 7).Value = "YES" Then
               
               ' Unlock the E:G cells so they stay editable (optional)
               Me.Range("E" & targetRow & ":G" & targetRow).Locked = False
               
               ' Lock all other cells in the row
               Me.Rows(targetRow).Locked = True
               ' Re-unlock E:G since we just locked the whole row
               Me.Range("E" & targetRow & ":G" & targetRow).Locked = False
               
               ' Protect the worksheet (add a password inside the quotes if needed)
               Me.Protect Password:="", UserInterfaceOnly:=True
           End If
       End If
   End Sub
   

5. Save your workbook as an .xlsm file (Macro-Enabled Workbook) to keep the VBA code active.

Key Notes: #

• The UserInterfaceOnly:=True parameter lets VBA modify cell locking without needing to unprotect the worksheet every time.
• Make sure your YES/NO dropdowns are set up with Data Validation (Data tab → Data Validation) to ensure only valid entries are entered—this prevents typos from breaking the formula/VBA.

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