如何设置条件格式：含指定文本/Total且数值符合条件时整行高亮

Got it, let's break down how to upgrade your conditional formatting to meet those exact requirements. You already have a rule that highlights rows containing "Total", and now we need to layer in the numeric check to adjust highlighting based on whether the row's total is above or below your target value (X,XXX).

Step 1: Select Your Target Range #

First, select the entire range where you want this formatting applied (e.g., A2:Z1000 – skip your header row if you don't want it included in the formatting).

Step 2: Create the "Total > X,XXX" Highlight Rule #

1. Go to the Home tab → Conditional Formatting → New Rule
2. Choose the option "Use a formula to determine which cells to format"
3. Paste this formula (adjust the range and target value to match your sheet):

   =AND(COUNTIF($A2:$Z2,"*Total*")>0, MAX($A2:$Z2)>1000)
   

   • Let's break this down:
     • $A2:$Z2: Locks the column references so the formula checks the entire row (replace with your actual row range)
     • *Total*: Uses wildcards to match any cell containing the text "Total" (remove wildcards if you need an exact match)
     • MAX($A2:$Z2): Grabs the largest numeric value in the row – if your total is in a specific column (e.g., column F), replace this with $F2 for accuracy
     • 1000: Replace this with your X,XXX target value (no commas in the formula, just the raw number)
4. Click Format, pick your highlight color (e.g., green for values above target), then hit OK twice to save the rule.

Step 3: Create the "Total < X,XXX" Highlight Rule #

Repeat the same process for rows where the total is below your target:

1. Go to Conditional Formatting → New Rule → "Use a formula..."
2. Use this formula:

   =AND(COUNTIF($A2:$Z2,"*Total*")>0, MAX($A2:$Z2)<1000)
   

3. Choose a different highlight color (e.g., red for values below target), then save the rule.

Step 4: Adjust Rule Priority (Optional but Recommended) #

1. Go to Conditional Formatting → Manage Rules
2. If you're keeping your original "contains Total" rule, move the two new rules above it in the list
3. Check the "Stop if true" box for each new rule to prevent overlapping formatting (since a row can't be both above and below the target, this is optional but keeps things clean)

Quick Edge Case Tips #

• If your sheet uses formatted numbers with commas, don't worry – Excel uses the underlying numeric value for the formula, so you don't need to adjust anything
• If you want to include rows where the total equals X,XXX, change the operators to >= or <= in the formulas

内容的提问来源于stack exchange，提问作者K. Harbison