RH

Select any cell which has the right color. Then double-click the format painter. Now you can go about clicking cells and they all get the same formatting. Press the Escape key to stop it.

Good day

Thank you for the awesome and quick response, however this is not really working as I thought… I would like to select green to fill all cells as necessary and the select the next colour and so forth.

This script is working on yellow, but I can’t seem to change the colour after that, and when I start with a different colour it only fills x2 cells and then stops.

Please see screenshot of the current values they are all random.

Thank you

Good day 

Thank you for the awesome feedback...it is not working exactly as I thought it would. Please see below the numbers I'm working with. I would like to select a colour then click on the cells to change (Green) after that is finished select the next colour (Blue) until I've highlighted all the blue values then the next colour. (The yellow option in the script seems to be working but I can't change the colour to the next one, and if I start with a different colour, it only fills x2 cells... (All numbers are totally random I might have x3 100 values with different colours.)

You can use the Format Painter tool to copy the cell format

but similar to the FILL color button you have to hit it each time so AFAIK a 'locked on' functionality for the Fill (or other excel functions) is not possible with existing excel functionality.  However, this can be achieved using either a macro or a script.  Because (IMHO) things are moving away from macros, here is the code for a script that will use the currently selected cell color and then highlight every other cell you click on that same color until you click on that original cell again ...

function main(workbook: ExcelScript.Workbook) {
    // Get the active cell and worksheet.
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();

    const origCell = selectedCell.getAddress();
    const selectedColor = selectedCell.getFormat().getFill().getColor();
    while (selectedCell.getAddress() == origCell) {
        selectedCell = workbook.getActiveCell();
    }

    while (selectedCell.getAddress() != origCell) {
    // Set fill color to yellow for the selected cell.
    selectedCell.getFormat().getFill().setColor(selectedColor);
    selectedCell = workbook.getActiveCell();
    }
   

}

to add this click on the Automate menu item -> New Script -> Create in Code Editor 

Highlight existing code and replace with the above.  Click on the pencil to rename the script to whatever you want to call it.  and then click save.

You then have the option to either just click on the run button from this automate menu window or you can add a button to the worksheet.

OK I updated the script to a) allow you to select and highlight ranges and b) to 'mark' the original cell using a grid pattern to make it easier to find and re-click it to end the script:

function main(workbook: ExcelScript.Workbook) {
    // Get the active cell and worksheet.
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();
    let tmpCell = selectedCell;
    const origCell = selectedCell;
    const origAddress = origCell.getAddress();
    const selectedColor = selectedCell.getFormat().getFill().getColor();


    while (selectedCell.getAddress() == origAddress) {
        selectedCell = workbook.getSelectedRange();
      origCell.getFormat().getFill().setPattern(ExcelScript.FillPattern.grid);
    }

    while (selectedCell.getAddress() != origAddress) {
      selectedCell.getFormat().getFill().setColor(selectedColor);
      selectedCell = workbook.getSelectedRange();
    }

    origCell.getFormat().getFill().setPattern(ExcelScript.FillPattern.solid);
}

here is a screen shot after I selected the green cell D4 and then selected the range D6:F10