Coloring variable cells in excel?

33 views (last 30 days)
Abdelrahman Taha
Abdelrahman Taha on 6 Mar 2020
Commented: Walter Roberson on 8 Mar 2020
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
  2 Comments
Abdelrahman Taha
Abdelrahman Taha on 6 Mar 2020
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!

Sign in to comment.

Answers (1)

Walter Roberson
Walter Roberson on 6 Mar 2020
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
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.
  4 Comments
Walter Roberson
Walter Roberson on 8 Mar 2020
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.

Sign in to comment.

Tags

Community Treasure Hunt

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

Start Hunting!