Formula in Excel Spreadsheet not work

Question

Hi Sir, 

Picture 1 shows the desired condition, where the row of the spreadsheet turns orange when the SPT (N) is more than 50. However, it will not work where the SPT (N) at row 47 become 0. All rows below row 48 become orange colour as picture 2. 

https://mega.nz/folder/g5dizZqR#7Pk7eyU0hXZTL6sj3qVJ-g

We attach the file in follows link for your further action. Please advise.

 

Thanks. 

Answer 1

Hi KAR FUNG, 

Welcome to Microsoft Q&A Forum. 

Thank you for sharing the details and the file. After reviewing your workbook, the issue occurs because the current conditional formatting rule uses a complex formula with and . When the SPT (N) value becomes 0 at row 47, this formula evaluates as TRUE for all subsequent rows, causing them to turn orange.VLOOKUPOFFSET

Please follow these steps:

1. Open Conditional Formatting Manager:
   • Go to Home > Conditional Formatting > Manage Rules.
     
   • Select This Worksheet to view all rules on the Default sheet.
2. Remove or Disable the Old Rule:
   • Find the rule applied to $B$21:$O$74 with a formula like: Ini

          IF(OR($E$
     

   • Select it and click Delete Rule (or uncheck to disable).
     
3. Create a New Rule for Column G (SPT):
   • Select the range you want to format ($B$21:$O$74).
   • Click New Rule > Use a formula to determine which cells to format.
   • Enter this formula:
     =``$G21``>50 (Lock the column with $, and use the first row number of your selected range.)
   • Click Format > Fill, choose orange (or use the previous color code FFFFC000).
   • Click OK to save.
     

After these steps, only rows where SPT (N) > 50 will turn orange, and changing one row will not affect others.

I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.  

Looking forward to hearing back from you with any updates or additional details. 

Warm regards, 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

Hi KAR FUNG,

First of all, I apologize for the delay in getting back to you, and thank you for accepting my previous answer, it means a lot. I also appreciate your clarification about wanting the orange highlight to appear only once at the first row where SPT (N) ≥ 50, not on every row that meets the condition.

To achieve this, you’ll need to remove the old conditional formatting rule and create a new one that checks for the first occurrence only.

Remove the Old Rule

• Go to Home > Conditional Formatting > Manage Rules.

Select This Worksheet to view all rules.

• Find the rule applied to $B$21:$O$74 and delete it (or uncheck to disable).

Create a New Rule

• Select the range you want to format.
• Click New Rule > Use a formula to determine which cells to format.
• Enter this formula:
• =AND($G21>=50, COUNTIF($G$21:$G21, ">=50")=1)

Explanation:

$G21>=50 checks if the current row meets the condition.

COUNTIF($G$21:$G21, ">=50")=1 ensures this is the first occurrence.

Set the Format

• Click Format > Fill, choose orange (or your preferred color).
• Click OK to save.

Wishing you a great day and success with your Excel project!