Running Excel Script to highlighted cells

Question

I am trying to save a script to apply a number format to whatever cells I have selected on the worksheet. Recording the script is easy, but it only applies to whatever cell I am in at the time. However, I could be in a different cell or I may need to apply the format to a range of cells.

This is a copy of my script at the moment but it is only being applied to cell E8, whereas I want it to run on whatever cells I have selected at the time.

Function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
Set format for range E8 on selectedSheet
    selectedSheet.getRange("E8").setNumberFormatLocal("#,##0;(#,##0);-");
}

I am not sure what I need to replace E8 with to get it to apply to other cells.

Thinkr · Accepted Answer · 2024-07-30 09:55:09Z

1

There is a method called getSelectedRanges() that allows you to target the selected range. Here is how you can use it in your case:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let selectedRange = selectedSheet.getSelectedRanges();
    for(let range of selectedRange){
        range.setNumberFormatLocal("#,##0;(#,##0);-")
    }
}

answered Jul 30, 2024 at 9:55

Thinkr

2022 silver badges11 bronze badges

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

4 Comments

Emma Emerson Over a year ago

Thank you for your reply. I am still doing something wrong as I have an error message.

Emma Emerson Over a year ago

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); let selectedRange = selectedSheet.getSelectedRanges(); range.setNumberFormatLocal("#,##0;(#,##0);-"); }

Emma Emerson Over a year ago

It says "see line 3, column 6: office scripts cannot infer the data type of this variable. Please declare a type for the variable.

Thinkr Over a year ago

@EmmaEmerson You forgot the for(let range of selectedRange){}. The script is:

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); let selectedRange = selectedSheet.getSelectedRanges(); for(let range of selectedRange){range.setNumberFormatLocal("#,##0;(#,##0);-")}}

The range variable is set in the for loop :)

Collectives™ on Stack Overflow

Running Excel Script to highlighted cells

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related