Forum Discussion
RH
Is it possible to select a color in Excel and highlight different cell by only clicking on them and not selecting fill the whole time?
1 Reply
- m_tarlerSilver Contributor
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