Conditional Formatting in Excel 2010

24 views (last 30 days)
Artyom
Artyom on 13 Mar 2013
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?

Answers (2)

Eric
Eric on 13 Mar 2013
Edited: Eric on 13 Mar 2013
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
  3 Comments
Marc Martinez Maestre
Marc Martinez Maestre on 16 Jul 2020
Edited: Marc Martinez Maestre on 16 Jul 2020
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')".

Sign in to comment.


Fernando Alcántara
Fernando Alcántara on 22 Dec 2017
%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?
  1 Comment
Marc Martinez Maestre
Marc Martinez Maestre on 16 Jul 2020
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()"

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by