Conditional striking in Excel

Question

Hello All, I need help in Excel where If the length (column A) is Greater or equal to 10 then letter "Y" in column B gets striked out and if its less than 10 letter "N" in column B gets striked out

Answer 1

To achieve the required conditional formatting you can follow the steps as is mentioned below:

---

• Select the range B2:B9 or adjust the range as per your suit.
• From Home Tab --> Under Styles Group --> Click Conditional Formatting --> Click New which opens the New Formatting Rule window. (Short Cut to Open --> ALT+H+L+N )
• Now, select the last rule --> Use a formula to determine which cells to format
• Enter the below formula in Edit the Rule Description

---

=(A2>=10)*(B2="Y")

---

• Click on Format --> Check the S̶t̶r̶i̶k̶e̶t̶h̶r̶o̶u̶g̶h̶ under Effects
• Press Ok twice.

---

The above would S̶t̶r̶i̶k̶e̶t̶h̶r̶o̶u̶g̶h̶ all those cells in Column A greater than or equal to 10 with Y in Column B, to perform the same for those less than 10 and have N in Column B repeat the process from steps 1 to 3 and enter the formula and lastly do the formatting, press ok twice. If you don't want to repeat the process, then select the range Column B and hit ALT+H+L+R , select the existing rule and hit Duplicate Rule and change the formula with the below one.

---

=(A2<>"")*(A2<10)*(B2="N")

---

Note that this would S̶t̶r̶i̶k̶e̶t̶h̶r̶o̶u̶g̶h̶ all N in Column B with values less than 10 but will ignore the ones with blank cells in Column A but if you want to consider the blanks then the above would be.

=(A2<10)*(B2="N")

---

---

---

EDIT: 6/26/2023

Per OP's Comments:

I have uploaded a new screenshot for your reference, where I have grouped every length with a Y & N, indicating there's a Y & a N for every individual length number in "Column A" and that if the length is less than 10 it should strike only N (as shown in B5 of the new screenshot, Since A4 is less than 10 I have striked N) the new screenshot is what I the result should look like.

---

• Formula used :

Green :

=(LOOKUP(2,1/(A$2:A2<>""),A$2:A2)<10)*(B2="N")

---

Blue:

=(LOOKUP(2,1/(A$2:A2<>""),A$2:A2)>=10)*(B2="Y")

---

Note that color fill only used to identify. You can ignore the color fill.

---

---

---