Excel Javascript API how to highlight selected cell current row and column

Question

I am trying to highlight the selected cell current row and column.

For VBA, I could use

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim iColor As Integer
On Error Resume Next

Cells.FormatConditions.Delete
iColor = 15
With Target.EntireRow.FormatConditions
     .Delete
     .Add xlExpression, , "TRUE"
     .Item(1).Interior.ColorIndex = iColor
     End With
With Target.EntireColumn.FormatConditions
     .Delete
     .Add xlExpression, , "TRUE"
     .Item(1).Interior.ColorIndex = iColor
     End With
End Sub

But, I need this in Excel online, so i try to achieve the same in script lab

Something like below

async function enableCellHighlight(): Promise < void> {
  await Excel.run(async function (context) {
    let workbook = context.workbook;
    workbook.onSelectionChanged.add(CellHighlightHandler);
    await context.sync();
  });
}
async function CellHighlightHandler(event: Excel.SelectionChangedEventArgs): Promise < void> {
  await Excel.run(async (context) => {
    let workbook = event.workbook;
    let selection = context.workbook.getSelectedRange();
    selection.load("address,rowIndex");
    await context.sync();
    selection.conditionalFormats.clearAll();
    let conditionalFormat = selection.getEntireRow().conditionalFormats.add(Excel.ConditionalFormatType.custom);
    conditionalFormat.custom.rule.formula = "=ROW()=" + (selection.rowIndex + 1);
    conditionalFormat.custom.format.fill.color = "red";
    console.log(`    Data changed address: ` + conditionalFormat.custom.rule.formula);
    await context.sync();
  });
}

But, this code will apply highlight for all previous selected row, it will not clear previous highlighted row after i click a new row cell.

Answer 1

from your code, when call "selection.conditionalFormats.clearAll()" it clear current selection's format but not the previous one. So you need to store the previous selection address and clear its format in current selection changed handler, one example sample code looks like below:

var previousSelectionAdress = "";

async function CellHighlightHandler(event: Excel.SelectionChangedEventArgs): Promise<void> {

  await Excel.run(async (context) => {

    let selection = context.workbook.getSelectedRange();

   context.workbook.worksheets.getActiveWorksheet().getRange(previousSelectionAdress).conditionalFormats.clearAll();

    selection.load("address,rowIndex");

    await context.sync();

    previousSelectionAdress = selection.address;

    let conditionalFormat = selection.getEntireRow().conditionalFormats.add(Excel.ConditionalFormatType.custom);

    conditionalFormat.custom.rule.formula = "=ROW()=" + (selection.rowIndex + 1);

    conditionalFormat.custom.format.fill.color = "red";

    console.log(`Data changed address: ` + conditionalFormat.custom.rule.formula);

    await context.sync();

  });

}