Asking for assistance creating a script to adding Conditional Formatting back to an Excel table after file update.

Question

Good Morning,

I have an Excel file that gets updated each night using a flow but when it is updated the Conditional Formatting rules are also removed. I am assuming that there is a way that the conditional formatting could be added back to the sheet with the use of a script but I am asking for help with this. I would appreciate any assistance. I have read Microsoft's literature but I unfortunately cannot grasp it well enough.

There is only one sheet in the file and there is only one table called Table1. Column A is where the user inputs a letter which denotes the color that the entire row should be highlighted.

Here is a screenshot, unfortunately I cannot upload a sample file because of our corporate rules.

Answer 1

Got it. Everything related to Excel goes to this forum, except for Excel scripts.

Thanks for clarifying.

Answer 2

Our category focus on excel function, we’d suggest you go to Microsoft Power Automate Community - Power Platform Community to post a new thread, which is the specific channel to handle Office script issue.

Answer 3

Thank you for that, unfortunately in this instance that solution will not work. The file cannot be a .xlsm file, it must stay .xlsx so an office script would be needed. But your results are exactly what I am looking for.

*edit

I think I got it, although I messed up the color:

function main(workbook: ExcelScript.Workbook) { let conditionalFormatting: ExcelScript.ConditionalFormat;let selectedSheet = workbook.getActiveWorksheet();// Create custom from range A2:R4210 on selectedSheetconditionalFormatting = selectedSheet.getRange("A2:R4210").addConditionalFormat(ExcelScript.ConditionalFormatType.custom);conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006");conditionalFormatting.getCustom().getFormat().getFill().setColor("#FFC7CE");conditionalFormatting.getCustom().getRule().setFormula("=$A2="r"");}

Answer 4

You may record a macro and run it when you need it.

====================

Sub Macro1()

Range("A2:R4210").Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=""r""" 

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 

With Selection.FormatConditions(1).Interior 

    .PatternColorIndex = xlAutomatic 

    .Color = 255 

    .TintAndShade = 0 

End With 

Selection.FormatConditions(1).StopIfTrue = False 

End Sub

====================

Here is the conditional formatting created by recorder macro.