Conditional formatting, using color scaling of red-yellow-green (red being highest) to an Excel sheet data from range 'D6:AV71' and saving it.

I've an Excel workbook with 12 Excel sheets. I want to open sheet number 2, select the range 'D6:AV71', then apply conditional formating > color Scaling > red-yellow-green on the data , save the excel sheet and close it.

so that it starts looking like the image as i've attached.

I've added an example data of my excel sheet as well

[Walter Roberson]

You will need to use either activeX or a .NET control for this. MATLAB does not provide any direct facilities to format xlsx sheets.

[Shashank]

% Create a COM server to control Excel
excel = actxserver('Excel.Application');
% Open the Excel file
workbook = excel.Workbooks.Open('myfilename.xlsx');
worksheet = workbook.Worksheets.Item(1);
% Define the range for conditional formatting
range = 'D6:AV71';
% Select the range
excelRange = worksheet.Range(range);
% Add color scale conditional formatting rule
cfRule = excelRange.FormatConditions.AddColorScale(3); % 3-color scale
% Customize the color scale
colorScale = cfRule.ColorScaleCriteria;
% Set the highest color to red (RGB: 255, 0, 0)
colorScale.Item(1).Type = 1; % 1: Highest Value
colorScale.Item(1).FormatColor.Color = RGB(255, 0, 0);
% Set the midpoint type to "4" for 3-color scale
colorScale.Item(2).Type = 4; % 4: Number
colorScale.Item(2).Value = 0; % Set midpoint value to 0
colorScale.Item(2).FormatColor.Color = RGB(255, 255, 0); % Yellow
% Set the lowest color to green (RGB: 0, 255, 0)
colorScale.Item(3).Type = 2; % 2: Lowest Value
colorScale.Item(3).FormatColor.Color = RGB(0, 255, 0);
% Save the Excel file (optional)
% workbook.Save;
% Close Excel (optional)
% workbook.Close;
% excel.Quit;
% excel.delete;
% Define a function to convert RGB values to Excel's RGB format
function rgb = RGB(r, g, b)
rgb = bitor(bitor(uint32(r), bitshift(uint32(g), 8)), bitshift(uint32(b), 16));
end

Hey thanks for the reply, but are we talking about something like this ?? cuz this doesn't work for me. Can you help me out the code?

[Walter Roberson]

I do not know anything about setting color gradients in Excel. Perhaps some of the information from https://www.mathworks.com/matlabcentral/answers/411623-how-to-fill-excel-cell-color-using-matlab-command-and-not-by-the-actxserver-method#answer_329948 might be of interest.

Answer 1

[Shubham]

Hey @Shashank,

I understand that you want to select sheet number 2 and apply conditional formatting to the range 'D6:AV71' and save the sheet.

For selecting a sheet in a workbook and bringing it to focus you can use handle of sheet from using “get” function and then selecting it. Try referring to the following MATLAB answer:

https://www.mathworks.com/matlabcentral/answers/100122-how-can-i-change-sheet-focus-within-excel-using-an-activex-component-in-matlab-7-10-r2010a

For conditional formatting you can modify your code as following:

%% Open the file
fname = 'datafile.xlsx';

%% Open the workbook, select a range and show Excel
Excel = actxserver('Excel.Application');
Excel.Workbooks.Open(fullfile(pwd, fname));
Range= Excel.Range('D6:AV71');
Excel.Visible = 1;
%% conditional formatting
cfRule = Range.FormatConditions.AddColorScale(3); % 3-color scale
% Customize the color scale
colorScale = cfRule.ColorScaleCriteria;
% Set the lowest color to green (RGB: 0, 255, 0)
colorScale.Item(1).Type = 1; % 1: Lowest Value
colorScale.Item(1).FormatColor.Color = RGB(0, 255, 0);
% Set the midpoint type to "4" for 3-color scale
colorScale.Item(2).Type = 4; % 4: Number
colorScale.Item(2).Value = 250; % Set midpoint value to (middle point you want to set)
colorScale.Item(2).FormatColor.Color = RGB(255, 255, 0); % Yellow
% Set the highest color to red (RGB: 255, 0, 0)
colorScale.Item(3).Type = 2; % 2: Highest Value
colorScale.Item(3).FormatColor.Color = RGB(255, 0, 0);
% Save the Excel file (optional)
% workbook.Save;
% Close Excel (optional)
% workbook.Close;
% excel.Quit;
% excel.delete;
% Define a function to convert RGB values to Excel's RGB format

%% Save and close the file
Excel.ActiveWorkbook.Save()
Excel.Quit()

function rgb = RGB(r, g, b)
rgb = bitor(bitor(uint32(r), bitshift(uint32(g), 8)), bitshift(uint32(b), 16));
end

I have modified your code and now the highest value cells obtain red colour while the lowest ones obtain green colour. The midpoint value is currently hardcoded to 250 however it can be changed as per the requirements. The code has produced the following output:

Hope this helps!!