Is it possible to select all cells where a conditional format is true?

Question

I want to check an excel worksheet if any of the cells have a conditional format that true. I have multiple formats that format cells different colors, but I just want to be able to see if any of the conditions are true (don't care which condition). This example isn't working, but I want to know if I'm on the right track or even possible to do what I'm asking.

var lastCell = Globals.ThisAddIn.Application.ActiveCell.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
var firstCell = ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[2,1];
var range = ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[firstCell, lastCell]
var r = range.SpecialCells(XlCellType.xlCellTypeAllFormatConditions, true);

Loop through the cells and add them to a list if they match condition? Or use linq if possible — EpicKip, Feb 06 '17 at 13:48

score 0 · Answer 1 · edited May 23 '17 at 12:01

As the FormatConditions collection of a range applies to an entire range, I don't believe there's a mechanism to determine the value for each cell in the range. I think your best bet would be to test the Formula in the cell of interest. I'm sure this is not what you wanted to hear.

Get the used range with this answer. Of course, replacing xlWorkSheet with ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).

Then, assuming you set it to range, loop through the range...

for (int r = 1; r <= range.Rows.Count; r++)
{
    for (int c = 1; c <= range.Columns.Count; c++)
    {

...replace the formula/value temporarily with something like (preface: I almost guarantee this code will not work)...

        string formula = range[r, c].Formula; //or Value, check HasFormula
        for (int i = 1; i <= range.FormatConditions.Count; i++)
        {
            range[r, c].Formula = range.FormatConditions[i].Formula1; // or Formula2 (?)
        }
        bool condition = range[r, c].Value;
        // do work based on the condition

        range[r, c].Formula = formula;

... and close the loops.

}
}

Is it possible to select all cells where a conditional format is true?

1 Answers1