Using ExcelScript change font name depending on cell value

Question

Using Excel Script how can you change the font name of cells on a sheet if they have a specific Value?

I need to change cells to font name "Wingdings" if the cell has a "ü" as the value. The regular font name is "Calibri". The sheet range is $1:$1048576.

Was originally doing VBA which I need to transfer to ExcelScript.

VBA code:

Sub FormatText(ByVal Target As Range)

If Target.Range("$1:$1048576") Then
    If Target.Value = "ü" Then
    
        Target.Font.Name = "Wingdings"
    
    Else
    
        Target.Font.Name = "Calibri"
    
    End If

End If

End Sub

You can adapt code to be found at learn.microsoft.com/en-us/office/dev/scripts/resources/samples/… - for changing the font name you could also try using the recorder — CHill60
– CHill60, Commented Jul 19, 2023 at 11:37

taller · Accepted Answer · 2023-07-20 16:18:34Z

0

This task can be accomplished using Excel script code. For optimal efficiency, the code should check only the relevant range of cells (UsedRange) instead of iterating through every cell on the worksheet.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    let targetRange = sheet.getUsedRange();
    targetRange.getFormat().getFont().setName("Calibri");
    let values = targetRange.getValues();
    values.forEach((rowArray, rowIndex) => {
        rowArray.forEach((cellValue, colIndex) => {
            if (cellValue === "ü") {
                let cell = targetRange.getCell(rowIndex, colIndex);
                cell.getFormat().getFont().setName("Wingdings");
            }
        });
    });
}

answered Jul 20, 2023 at 16:18

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using ExcelScript change font name depending on cell value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related