How to test whether any non-header cell in Excel file is bold or italic.

Answer 1

This should do the trick, enjoy. Keep in mind that putting in new Range Names will not trigger a recalc of a cell containing this function (so hit F9 after creating range names).

Option Explicit

Public Function IsDataCellBoldOrItalic() As Boolean
    Dim rngName As Name
    Dim intersectRange As Name

    For Each rngName In ActiveWorkbook.Names
        If Not Intersect(rngName.RefersToRange, Application.ThisCell) Is Nothing Then
            IsDataCellBoldOrItalic = False
            Exit Function
        End If
    Next

    ''# Now we know we are not in a "header" cell
    IsDataCellBoldOrItalic = Application.ThisCell.Font.Bold Or Application.ThisCell.Font.Italic

End Function

Answer 2

Here is my solution. I've tested it with some Excel 2007 files (.xlsx). The program can be built using VS 2010 (targeting .NET 4) with the following four references: Microsoft.CSharp, Microsoft.Office.Interop.Excel, System and System.Core.

Using .NET 4 makes it a bit easier to work with Excel.

Anyway here's the code:

using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application excelapplication = null;
            Excel.Workbook workbook = null;

            try
            {
                excelapplication = new Excel.Application();
                workbook = excelapplication.Workbooks.Open(args[0]);
                var errors = new Dictionary<string, List<string>>();
                foreach (Excel.Worksheet sheet in workbook.Sheets)
                {
                    int rowCount = sheet.UsedRange.Cells.Rows.Count;
                    int colCount = sheet.UsedRange.Cells.Columns.Count;
                    var usedCells = sheet.UsedRange.Cells;

                    for (int i = 1; i <= rowCount; i++)
                    {
                        for (int j = 1; j <= colCount; j++)
                        {
                            Excel.Range range = usedCells[i, j];
                            List<string> cellErrors;
                            if (HasNonDefaultFont(range, out cellErrors))
                            {
                                if (!IsHeaderCell(workbook, range))
                                {
                                    string cellDisplayTitle = String.Format("{0}!{1}", sheet.Name, range.Address);
                                    errors[cellDisplayTitle] = cellErrors;
                                }
                            }
                        }
                    }
                }
                ReportErrors(errors);
            }
            finally
            {
                if (workbook != null)
                    workbook.Close();
                if (excelapplication != null)
                    excelapplication.Quit();
            }
        }

        static bool HasNonDefaultFont(Excel.Range range, out List<string> differences)
        {
            differences = new List<string>();

            if (range.Font.Color != 0.0)
                differences.Add("Has font-color");

            if (range.Font.Bold)
                differences.Add("Is bold");

            if (range.Font.Italic)
                differences.Add("Is italic");

            if (range.Font.Underline != (int)Microsoft.Office.Interop.Excel.XlUnderlineStyle.xlUnderlineStyleNone)
                differences.Add("Is underline");

            if (range.Font.Strikethrough)
                differences.Add("Is strikethrough");

            if (range.Font.Name != "Arial")
                differences.Add(String.Format("Font is {0}", range.Font.Name));

            if (range.Font.Size != 10)
                differences.Add(String.Format("Font size is {0}", range.Font.Size));

            return differences.Count != 0;
        }

        static bool IsHeaderCell(Excel.Workbook workbook, Excel.Range range)
        {
            // Look through workbook names:
            foreach (Excel.Name namedRange in workbook.Names)
            {
                if (range.Parent == namedRange.RefersToRange.Parent && range.Application.Intersect(range, namedRange.RefersToRange) != null)
                    return true;
            }

            // Look through worksheet-names.
            foreach (Excel.Name namedRange in range.Worksheet.Names)
            {
                if (range.Parent == namedRange.RefersToRange.Parent && range.Worksheet.Application.Intersect(range, namedRange.RefersToRange) != null)
                    return true;
            }
            return false;
        }

        static void ReportErrors(Dictionary<string, List<string>> errors)
        {
            if (errors.Count > 0)
            {
                Console.WriteLine("Found the following errors:");
                Console.WriteLine("---------------------------------");
                Console.WriteLine("{0,-15} | Error", "Cell");
                Console.WriteLine("---------------------------------");
            }

            foreach (KeyValuePair<string, List<string>> kv in errors)
                Console.WriteLine("{0,-15} | {1}", kv.Key, kv.Value.Aggregate((e, s) => e + ", " + s));
        }
    }
}

The program assumes the name of an excel-file as its first argument. This file is opened and each cell is tested against different font-criteria. The cells with "non-default-font" is tested against the named ranged, and those that fall outside of these ranges are output to the console.

As usual some errorhandling should be added to the program - but hopefully this should get you started.