Conditional Formatting with Scripts and Power Automate

Microsoft

I have an Excel Spreadsheet that two users use each day.

They do a LOT of ctrl C and ctrl V.

Because of this, it starts messing with the conditional formats we have.

My idea was to create a script inside of Excel Online, save it, then user Power Automate each night to run that script to fix all the conditional formats.

The script would delete the CFs from two sections of the sheet, and then recreate them.

I have zero idea how to write these scripts, so I was using the record actions feature.

It records me creating the CF, but when I run the script to test it, it always errors.

See attached.

What I am not understanding is I am not changing these scripts after I stop recording so I am not sure what is happening.

 

Goal:

Script the following:

Delete all rules in section J3:L55

Delete all rules in section M3:AG66

 

For section J3:L55, create a rule that highlights cells that have text like:

Sun-Wed

OO*

OFF*

Wed-Sat

Med*

etc..

 

For the other sections, I have a rule for each column that turns the cells black if a certain cell is a Weekend.  The actual condition looks like this:  

Apply to range M3:M55

=WEEKDAY(R2C13,2)>5

 

Then I do that for every other column (the C changes in the formula above).

 

When I try to create these with the recorder they are failing.

 

3 Replies
Ok, looks like the record action for the second section (marking it as weekend) is working. i was able to record that, delete the condition, and run the script, that worked. I still need assistance with the text conditions in the first section
Is there a limit with scripts? I found if I run the following my conditions work up to a certain column, then they dont work. If I clear the rules for the entire sheet, and enter the formatting into a column that was not working, it will work. let selectedSheet = workbook.getActiveWorksheet(); // Clear conditional formats from range M:AG on selectedSheet selectedSheet.getRange("M:AG").clearAllConditionalFormats(); let conditionalFormatting: ExcelScript.ConditionalFormat; // Change custom from range M3:M55 on selectedSheet conditionalFormatting = selectedSheet.getRange("M3:M55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+1,2)>5"); // Change custom from range N3:N55 on selectedSheet conditionalFormatting = selectedSheet.getRange("N3:N55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+2,2)>5"); // Change custom from range O3:O55 on selectedSheet conditionalFormatting = selectedSheet.getRange("O3:O55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+3,2)>5"); // Change custom from range P3:P55 on selectedSheet conditionalFormatting = selectedSheet.getRange("P3:P55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+4,2)>5"); // Change custom from range Q3:Q55 on selectedSheet conditionalFormatting = selectedSheet.getRange("Q3:Q55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+5,2)>5"); // Change custom from range R3:R55 on selectedSheet conditionalFormatting = selectedSheet.getRange("R3:R55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+6,2)>5"); // Change custom from range S3:S55 on selectedSheet conditionalFormatting = selectedSheet.getRange("S3:S55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+7,2)>5"); // Change custom from range T3:T55 on selectedSheet conditionalFormatting = selectedSheet.getRange("T3:T55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+8,2)>5"); // Change custom from range U3:U55 on selectedSheet conditionalFormatting = selectedSheet.getRange("U3:U55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+9,2)>5"); // Change custom from range V3:V55 on selectedSheet conditionalFormatting = selectedSheet.getRange("V3:V55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+10,2)>5"); // Change custom from range W3:W55 on selectedSheet conditionalFormatting = selectedSheet.getRange("W3:W55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+11,2)>5"); // Change custom from range X3:X55 on selectedSheet conditionalFormatting = selectedSheet.getRange("X3:X55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+12,2)>5"); // Change custom from range Y3:Y55 on selectedSheet conditionalFormatting = selectedSheet.getRange("Y3:Y55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+13,2)>5"); // Change custom from range Z3:Z55 on selectedSheet conditionalFormatting = selectedSheet.getRange("Z3:Z55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+14,2)>5"); // Change custom from range AA3:AA55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AA3:AA55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+15,2)>5"); // Change custom from range AB3:AB55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AB3:AB55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+16,2)>5"); // Change custom from range AC3:AC55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AC3:AC55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+17,2)>5"); // Change custom from range AD3:AD55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AD3:AD55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+18,2)>5"); // Change custom from range AE3:AE55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AE3:AE55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+19,2)>5"); // Change custom from range AF3:AF55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AF3:AF55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+20,2)>5"); // Change custom from range AG3:AG55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AG3:AG55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+21,2)>5");
I fixed all the issues except for the conditions of text, if anyone can help me out