Coloring variable cells in excel?

Hi,

I am writing output of some caluations in excel, and I need to color certain cells in red based on some conditions. How could I do that if i don't know a priori which cells to be colored?

Thanks

[Walter Roberson]

MATLAB does not provide any function for this purpose. You will need to use ActiveX to talk to excel and command it to select the cells you want to color and apply color to the current cells.

[Abdelrahman Taha]

I know that. My quesion is about when the cells to be colored are variables, which means I can't know which cells to change their colors beforehand. It all depends on the contents of the cell. So, let's say I want to write a 5X5 matrix in excel. And I want the cells of the last column to be colred in red if their values exceed a certain value, how could I do that?. So, the problem is that I can't name the range of cells or the cell to be colored like 'D1' or 'D1:D5', I can't do that!

Answer 1

[Walter Roberson]

So, let's say I want to write a 5X5 matrix in excel. And I want the cells of the last column to be colred in red if their values exceed a certain value, how could I do that?

Read the matrix from Excel. Do whatever test is appropriate, getting back a logical matrix the same size indicating whether each individual cell is to be colored the way you are concerned about at the time.

find() on the logical matrix using the two-output form of find(), returning row and column numbers.

Now, use one of

https://www.mathworks.com/matlabcentral/fileexchange/28343-column-converter-for-excel

https://www.mathworks.com/matlabcentral/fileexchange/33905-convert-row-column-number-into-xlswrite-compatible-format

https://www.mathworks.com/matlabcentral/fileexchange/58917-getexcelrange-rowlimits-columnlimits

or similar File Exchange contributions, to convert the row and column numbers to excel cell name form.

You can now use a technique such as at https://www.mathworks.com/matlabcentral/answers/3352-how-to-set-excel-cell-color-to-red-from-matlab#answer_4983 to color the cells, one at a time. Where Jiro wrote

WB.Worksheets.Item(1).Range('A1').Interior.ColorIndex = 3;

you would pass the variable containing the Excel reference instead of hard-coding 'A1' .

For arrays that are no more than 26 columns, you can simplify the code by not using the File Exchange contributions to create the cell references:

L = 'A' : 'Z';
all_cell_references = compose('%s%d', L(column_numbers).', row_numbers(:));  %r2016b or later
for K = 1 : length(all_cell_references)
    WB.Worksheets.Item(1).Range(all_cell_references{K}).Interior.ColorIndex = 3;
end

Make sure you re-check the color indices: My Answer in the referenced post gives a link to the list of colors.

[Abdelrahman Taha]

Thanks so much for your detailed explanation. But I've tried all the methods explained in these attached links and none of them worked. I always get an error that they're unrecognized functions or variables!

[Walter Roberson]

Did you start right from

Excel = actxserver('excel.application');

and even that said no such function or variable? If so then that would indicate that you are not working on MS Windows. If you are not working on MS Windows then there is no way to use ActiveX to talk directly to Excel, and the task would get rather complicated.

[Abdelrahman Taha]

Yes, I am working on MS Windows and I did start from (Excel = actxserver('excel.application');)

I think it's supposed to be a defined function in matlab that converts a num to an excel cell name and that there's no need to talk to excel beforehand.

[Walter Roberson]

If you were to dig far enough into the internal code of routines such as readtable, it is plausible that you might find an internal package function that converts numbers to column names. However there is no documented MATLAB function for that purpose.

I gave links to a few File Exchange contributions that can do the translation, and I gave code for it that works for up to 26 columns.