Conditional formatting, using color scaling of red-yellow-green (red being highest) to an Excel sheet data from range 'D6:AV71' and saving it.
39 views (last 30 days)
Show older comments
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
3 Comments
Walter Roberson
on 5 Oct 2023
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.
Accepted Answer
Shubham
on 13 Oct 2023
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:
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!!
0 Comments
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!