title: Detect and highlight #N/A errors in Excel File C#
body: <p>I'm trying to read an excel file and highlight the cells with a color with #N/A errors in all the sheets , However i've not been able to find a breakthrough.</p>
  <p>Referred to the below article with no luck</p>
  <p><a href="https://stackoverflow.com/questions/2424718/how-to-know-if-a-cell-has-an-error-in-the-formula-in-c-sharp">How to know if a cell has an error in the formula in C#</a></p>
  <p><strong>Code</strong></p>
  <pre><code>public void ExcelScanInternal()
          {
              //
              // Get sheet Count and store the number of sheets.
              //
              //int RowNo = 0;
  
              xlApp = new xl.Application();
              workbooks = xlApp.Workbooks;
              workbook = workbooks.Open(sFileName);
  
  
              int numSheets = workbook.Sheets.Count;
  
              try
              {
                  for (int sheetNum = 1; sheetNum &lt; numSheets + 1; sheetNum++)
                  {
                      xl.Worksheet sheet = (xl.Worksheet)workbook.Sheets[sheetNum];
                      xl.Range range = sheet.UsedRange;
                      //range = sheet.Cells.SpecialCells(xl.XlCellType.xlCellTypeFormulas, xl.XlSpecialCellsValue.xlErrors);
                      //select all the cells with error formula
                      
  
                      for (int i = 3; i &lt;= range.Rows.Count; i++)
                      {
                          for (int j = 1; j &lt;= range.Columns.Count; j++)
                          {
                              /*if (range.Cells[i, j] != null &amp;&amp; range.Cells[i, j].Value2 != null)
                              //{ 
                                  if(range.Formula= &quot;#N/A&quot;)
                                  {
                                  range.Select();
                                  range.Borders.LineStyle = xl.XlLineStyle.xlDot;
                                  range.Borders.Color = ColorTranslator.ToOle(Color.Red);
                                  }
                              }*/
                              bool isNA = xlApp.WorksheetFunction.IsNA(range.Cells[i, j]);
                              
                              if (isNA)
                              {
                                  range.Select();
                                  range.Borders.LineStyle = xl.XlLineStyle.xlDot;
                                  range.Borders.Color = ColorTranslator.ToOle(Color.Red);
                              }
  
                              workbook.Save();
                              CloseXcel();
                          }
                      }
  
                  }
              }
              catch (Exception ex)
              {
                  MessageBox.Show(ex.Message);
                  MessageBox.Show(ex.StackTrace);
              }
  
          }
  </code></pre>
  <p>Below are the errors that I got in the process of testing</p>
  <p><a href="https://i.stack.imgur.com/FgFqg.png" rel="nofollow noreferrer">error</a>
  <a href="https://i.stack.imgur.com/ogbAP.png" rel="nofollow noreferrer">stack trace</a></p>
  <p>Also read about Excel <em><strong>WorksheetFunction.IsNA</strong></em> , not sure how to use it in this scenario.</p>
  <p><a href="https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.isna" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.isna</a></p>
  <p>Any help would be appreciated</p>