Hi @Eric Cartagena,

Welcome to the Microsoft Q&A forum.

I would like to provide you with the following information:

I have updated the Conditional Formatting formula in your sheet according to the requirements you mentioned.

The changes I made are as follows:

• Rows 1 and 2 in your sheet were incorrect, so I inserted two new rows and removed the original rows 1 and 2.
• Since the formatting in rows 3 and 4 was correct, I copied this formatting and applied it to the remaining rows.

You can also follow the steps below to apply the formatting yourself:

How to copy the formatting:

• Step 1: Select a row that has the correct formatting.
• Step 2: Click Format Painter and drag it across the other rows.

Next, please click on cell E2 (the first cell of the column) and follow the steps below to create a Conditional Formatting rule. This rule will display the Queue Duplicate cell in red when the cells to the left contain duplicate values.

Note: Conditional Formatting depends on the first cell where the rule is applied. Therefore, you should apply the rule to the first cell and then drag it to the other cells, instead of selecting the entire column from the beginning.

How to create the Conditional Formatting rule:

• Step 1: Click on cell E2 > select Conditional Formatting.
• Step 2: Select New Rule > enter the following formula: =COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2,$E:$E,$E2)>1
• 

And this is the result after applying the rule.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.   

Thank you for your patience and your understanding. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.

I look forward to continuing the conversation.

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. 

In this scenario the formulas and conditional formatting rules most likely stopped working because either:

1. Workbook calculation is set to Manual, so formulas are not recalculating.
2. One or more formulas or references used by the conditional formatting rules are now broken (for example, #REF! or #NAME? errors due to deleted sheets/columns, renamed sheets, or changed ranges).

Use the following checks and fixes in order:

1. Verify automatic calculation is enabled
   1. In Excel, select File > Options.
   2. Go to the Formulas category.
   3. In Calculation options under Workbook Calculation, make sure Automatic is selected.
   4. Click OK, then press Ctrl+Alt+F9 to force a full recalculation and test whether the queue highlighting now works.
2. Check for pound (#) errors in the key columns Conditional formatting rules that rely on formulas will stop working correctly if the underlying formulas return errors such as #REF!, #NAME?, #VALUE!, etc.
   1. On the Service Queue sheet, scan the columns that store Name, TCT, Date Received, Service Type, and any helper columns used for duplicate/completed checks.
   2. Look for any of these error values: #REF!, #NAME?, #VALUE!, #N/A, #NUM!, #DIV/0!, #NULL!.
   3. If any are present, use the specific guidance for that error type (for example, Correct a #REF! error or Correct a #NAME? error) to fix the formula or reference.
3. Check for broken references to other sheets or workbooks Since the logic depends on comparing Service Queue rows to rows on monthly tabs:
   1. Confirm that all monthly sheets still exist and have the same names as when the rules were created.
      • If any sheet was renamed and its name contains spaces or non-alphabetical characters, ensure references use single quotes, for example: ='January 2025'!A1:D1. Missing quotes can cause #NAME? errors.
   2. If any sheet was deleted or columns were removed, formulas may now show #REF!. For each affected formula:
      • Select the cell with #REF!.
      • In the formula bar, select #REF!, delete it, and re-enter the correct range reference.
   3. If any formulas reference an external workbook (for example, if the design was later extended to another file), verify the path syntax:
      • Workbook name in square brackets: [Q2 Operations.xlsx]Sales!A1:A8.
      • If using a full path and it contains spaces, enclose the entire path and sheet in single quotes, for example: =ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8).
4. Inspect the conditional formatting rules themselves The red fill and strikethrough are almost certainly driven by conditional formatting formulas.
   1. Go to the Service Queue sheet.
   2. On the Home tab, select Conditional Formatting > Manage Rules.
   3. In Show formatting rules for, choose This Worksheet.
   4. Identify the rules that:
      • Color the “Queue Duplicate” column red.
      • Apply red font and strikethrough to completed services.
   5. For each rule, check the Applies to range and the Formula:
      • Ensure the Applies to range still covers all relevant rows/columns (for example, if rows were added, the range might still stop at an old row number).
      • Open the formula and check for:
        • #REF! or #NAME? inside the formula.
        • References to old sheet names or columns that no longer exist.
   6. Correct any broken references and expand the Applies to range if needed.
5. Check for compatibility or function issues if the file was saved to an older format If the workbook was saved as an older Excel format (for example, .xls for Excel 97–2003), some functions or references may have been downgraded:
   1. Run the Compatibility Checker (when saving to an older format) and review any warnings about:
      • Functions not available in earlier versions that will become #NAME?.
      • References outside the older row/column limits that will be adjusted.
   2. If such issues are reported, adjust the formulas or keep the file in a modern .xlsx format so the original functions continue to work.
6. If nothing obvious is broken, test with a new row
   1. On Service Queue, add a new test row with a unique Name, TCT, Date Received, and Service Type.
   2. Copy the same four values into another row on Service Queue to see if the duplicate rule triggers.
   3. Copy the same four values into one of the monthly tabs to see if the “completed” rule triggers.
   • If the new rows work but old ones do not, the issue is likely with the Applies to ranges.
   • If nothing works, focus again on calculation mode and formula errors.

If after these steps formulas still do not react, the next step is to compare the current conditional formatting formulas and ranges against a known-good earlier version of the file (if available) and restore the original rules.

References:

• How to avoid broken formulas in Excel
• Formula compatibility issues in Excel
• Manage workbook links
• How to fix formula errors in Excel for Mac