如何从指定行开始选中每隔N行，修改字体颜色与样式？

I’ll show you two simple ways to make this happen—one using built-in tools (no coding needed) and another with a quick script for automation.

Method 1: Conditional Formatting (No Code Required) #

This is the easiest way if you want a dynamic solution that updates automatically when you edit cells:

• First, select the entire range of cells you want to apply formatting to (start from row 5 and cover all relevant columns).
• Go to the Home tab → click Conditional Formatting → choose New Rule.
• In the rule dialog, select "Use a formula to determine which cells to format".
• Paste this formula (replace A1 with the top-left cell of your selected range—e.g., if you started at E5, use E5):

  =AND(MOD(ROW()-5,4)=0, TRIM(A1)<>"")
  

  Let me break this down:
  • ROW()-5 calculates the offset from row 5
  • MOD(...,4)=0 ensures we target every 4th row after that (rows 5, 9, 13, etc.)
  • TRIM(A1)<>"" checks that the cell has actual text (ignores empty cells with just spaces)
• Click Format, switch to the Font tab, set the font to bold and red, then hit OK.
• Click OK again to apply the rule—boom, all your "Edit Access" rows (and any other non-empty cells in those positions) will be bold red!

Method 2: VBA Script for Batch Automation #

If you need to apply this formatting once to a large dataset or want to automate it for future use:

• Press Alt + F11 to open the VBA Editor.
• Right-click your workbook name in the Project Explorer → Insert → Module.
• Paste this code (adjust the sheet name and column to match your data):

  Sub FormatTargetRows()
      Dim ws As Worksheet
      Dim lastRow As Long
      Dim currentRow As Long
      
      ' Update "Sheet1" to your actual worksheet name
      Set ws = ThisWorkbook.Worksheets("Sheet1")
      ' Update "A" to your target column letter
      lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      
      ' Loop from row 5, stepping 4 rows each time
      For currentRow = 5 To lastRow Step 4
          ' Check if cell isn't empty
          If Trim(ws.Cells(currentRow, "A").Value) <> "" Then
              With ws.Cells(currentRow, "A").Font
                  .Bold = True
                  .Color = RGB(255, 0, 0) ' Red color
              End With
          End If
      Next currentRow
  End Sub
  

• Modify the sheet name (Sheet1) and column (A) to match your dataset.
• Press F5 to run the script, or go back to Excel, open the Developer tab → Macros, select FormatTargetRows and hit Run.

Quick Note for Multiple Columns #

If you need to apply this to multiple columns:

• For conditional formatting: Just select all the columns you need—relative cell references in the formula will adapt automatically.
• For VBA: Add a loop over columns, or change ws.Cells(currentRow, "A") to a range like ws.Range(ws.Cells(currentRow, "A"), ws.Cells(currentRow, "D")) to cover columns A-D.

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