Conditional Formatting in Excel 2010

Hi. How to apply conditional formatting in Excel 2010?

 xlCellValue = 1;
 Excel.Selection.FormatConditions.Delete;
 Excel.Selection.FormatConditions.Add(xlCellValue,1,'0','1');

As I understand this is the code for Excel 2003. In Excel 2010 there are FormatCondition Methods. One of the methods is Modify. I have tried to use it, but have an error:

 No appropriate method, property, or field Modify for class Interface.Microsoft_Excel_15.0_Object_Library.FormatConditions.

Can anyone help me?

Answer 1

[Eric]

It's not clear what formatting you're trying to set. Also, you don't state how you used the Modify() method to generate the error. The following worked for me. Hopefully it's helpful or at least instructive enough to get you started.

Excel = actxserver('Excel.Application');
Excel.Visible = true
xlCellValue = 1;
xlGreater = 5;
Excel.Workbooks.Add();
Range = Excel.Worksheets.Item(1).Range('A1:A4');
%Type some numbers into cells A1 through A4 by hand
Range.FormatConditions.Delete()
Range.FormatConditions.Add(xlCellValue, xlGreater, '=2.5')
Range.FormatConditions.Item(1).SetFirstPriority()
Range.FormatConditions.Item(1).Font.Color = -16383844
Range.FormatConditions.Item(1).Font.TintAndShade = 0

Some thoughts:

1. I recommend against using Excel.Selection. This can cause problems if more than one instance of Excel is running and will cause problems if the user does anything to change the selection. Your code will be much more robust if you define and use Range objects separately.

2. I've done a lot of interacting with Excel from Matlab and have never found it useful to use Excel's built-in conditional formating. I do all of the conditional operations in Matlab and format cells appropriately from Matlab. You might try that instead. That being said, I can see how using the conditional formatting in Excel might be useful at times.

Good luck,

Eric

[Artyom]

Thank you for your answer. Problem solved.

[Dawoud Khalifa]

Hi Eric, I think your code is what I need. I used matlab to generate an excel file, it has 13 sheets. In the last sheet, I named'table', I need to do some conditional formatting based on the value of the cell. I can do it from excel, but I would like to do it from matlab, because i will need to do it several more times. But, I donnot know how to use your code, I cannot understand how it will know the file name, and which sheet inside. Any help would be appreciated. Best Regards, Dawoud

[Marc Martinez Maestre]

It appears an error when I try using this solution. That the parameter is wrong, at the line "Range.FormatConditions.Add(xlCellValue, xlGreater, '=2.5')".

Answer 2

[Fernando Alcántara]

    %Connect to Excel
    ExcelApp = actxserver('excel.application');
    ExcelApp.Visible = true;%1;%1 es para hacerlo visible
    %Get Workbook object
    NewWorkbook=ExcelApp.Workbooks.Open([carpetaCSV, '\', fileCSV, '.xlsx']);   
    NewSheet=NewWorkbook.Sheets.Item(1);
    Range=NewSheet.Range('A1:D55');
    %%%XlFormatConditionOperator
    xlBetween =  1;
    %%%XlFormatConditionType
    xlCellValue = 1;
    Range.FormatConditions.Add(xlCellValue, xlBetween, '10', '50');
    Range.FormatConditions.Item(1).Interior.ColorIndex = 3;

My next question is, how to define multiple conditional formating? I tried defining multiple Range.FormatConditions.Add but it didn´t work. Any idea?

[Marc Martinez Maestre]

This works perfectly, thank you so much. I would say deleting the format after applying it and create a new one, inside a loop. Using the line from the code of Eirc: "Range.FormatConditions.Delete()"