# Color code the cells in excel, using a colorbar

3 visualizaciones (últimos 30 días)
Yekta Kamali el 30 de Jun. de 2021
Respondida: Vaibhav el 25 de Abr. de 2024
Hello,
An automated code compares the values of multiple matrixes (with values of zeros and ones) and creates a new matrix and specifies how many of the matrixes had one in each cell.
For example:
A=[ 0 0 0 0 ; 0 1 1 0 ; 0 1 1 0 ; 0 0 0 0];
B=[ 1 0 1 0 ; 0 1 0 0 ; 0 1 1 0 ; 1 1 1 1];
C=[ 0 1 1 1 ; 0 1 1 0 ; 0 0 1 0 ; 0 0 1 1];
D=[ 1 1 2 1 ; 0 3 2 0 ; 0 2 3 0 ; 1 1 2 2]; % after checking it creates D
Now, after writing this matrix in excel using
xlswrite('Binary_Analysis.xlsx',D);
I want to color code the cells in excel, using a colorbar, from red=min to green=Max number (remember the number of matrixes is a variable, in this case, A, B, and C)
Using something like
Excel = actxserver('excel.application'); % Connect to Excel
WB = Excel.Workbooks.Open(fullfile(pwd, 'Binary_Analysis.xlsx','B1:M10'),0,false); % Get Workbook object
% Set the color of cell "A1" of Sheet 1 to RED
WB.Worksheets.Item(1).Range('A1').Interior.ColorIndex = 3;
WB.Save(); % Save Workbook
WB.Close(); % Close Workbook
Excel.Quit(); % Quit Excel
This example specifically changes the color of cell A1 but I want a loop that checks the values in each cell and then indicates a color.
Another thing, I specifically said to read the excel file B1:M10 but I do not know the size of each matrix in advance. I just wanted to exclude the axis of the matrix. Do not read the first column and last row.
This is an automated code so it checks the size of each matrix.
Thank you
##### 0 comentariosMostrar -2 comentarios más antiguosOcultar -2 comentarios más antiguos

Iniciar sesión para comentar.

### Respuestas (1)

Vaibhav el 25 de Abr. de 2024
Hi Yekta
I understand that you would like to create a loop that checks the values in each cell and then indicates a colour. You can adjust the initial code by dynamically determining the range of cells to color code based on the size of the matrix.
Here is a code snippet for your reference:
A=[ 0 0 0 0 ; 0 1 1 0 ; 0 1 1 0 ; 0 0 0 0];
B=[ 1 0 1 0 ; 0 1 0 0 ; 0 1 1 0 ; 1 1 1 1];
C=[ 0 1 1 1 ; 0 1 1 0 ; 0 0 1 0 ; 0 0 1 1];
D=[ 1 1 2 1 ; 0 3 2 0 ; 0 2 3 0 ; 1 1 2 2]; % after checking it creates D
xlswrite('Binary_Analysis.xlsx', D);
% Connect to Excel
Excel = actxserver('excel.application');
Excel.Visible = true; % Optional: make Excel visible
WB = Excel.Workbooks.Open(fullfile(pwd, 'Binary_Analysis.xlsx'));
% Get the used range, excluding the first column and last row
sheet = WB.Worksheets.Item(1);
usedRange = sheet.UsedRange;
numRows = size(D, 1) - 1; % Assuming D is the matrix you wrote to Excel
numCols = size(D, 2) - 1;
% Loop through each cell in the specified range
for i = 2:numRows+1 % Start from 2 to exclude the first column
for j = 1:numCols
cellValue = sheet.Cells.Item(i, j).Value;
% Map cellValue to a color
% Assuming cellValue ranges from 0 to max(D(:)), you can adjust the color mapping logic as needed
if cellValue == min(D(:))
colorIndex = 3; % Red for min value
elseif cellValue == max(D(:))
colorIndex = 4; % Green for max value
else
% For intermediate values, you might need a more complex mapping
% This is a simple linear interpolation between red and green
% More sophisticated mappings may require custom RGB values
colorIndex = round(3 + (cellValue - min(D(:))) / (max(D(:)) - min(D(:))) * (4 - 3));
end
sheet.Cells.Item(i, j).Interior.ColorIndex = colorIndex;
end
end
% Save and close
WB.Save();
WB.Close();
Excel.Quit();
Hope this gets you started!
##### 0 comentariosMostrar -2 comentarios más antiguosOcultar -2 comentarios más antiguos

Iniciar sesión para comentar.

### Categorías

Más información sobre Spreadsheets en Help Center y File Exchange.

### Community Treasure Hunt

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

Start Hunting!

Translated by