0

I'm going to highlight the row of the current selected cell each time I select a new cell. And I found that there is a way to create a name based on active cell. that is:

  1. select cell A1.
  2. select formula>>name manager>>new then create a new name =!A1, named this_cell.

however if I refer to the name this_cell when creating conditional formatting rule, all the rows are highlighted.

Is there a solution for this?

And I don't want to use VBA as that may clear the undo stack.

Woods Chen
  • 574
  • 3
  • 13
  • Re And I don't want to use VBA as that may clear the undo stack - a UDF (that just returns information about a cell) will not reset the Undo stack – chris neilsen Jun 06 '19 at 05:22
  • Thank you @chrisneilsen, I wanna try this way, however after I create a function to return the row number of current selected cell and set the conditional formatting rules, the conditioned format won't update automatically when I select another cell. then how can I deal with this? – Woods Chen Jun 06 '19 at 06:52
  • I don't think there is a solution without VBA. Maybe this is what may help you: https://trumpexcel.com/highlight-active-row-column-excel/ – www.admiraalit.nl Jun 06 '19 at 07:55
  • @WoodsChen you need a `Selection_Change` event to force a recalculation. See [1](https://www.extendoffice.com/documents/excel/1494-excel-highlight-active-row-and-column.html) [2](https://stackoverflow.com/questions/22349424/highlight-active-row-column-in-excel-without-using-vba) [3](https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/highlight-the-active-cell-row-or-column) [4](https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/highlight-the-active-cell-row-or-column) – chris neilsen Jun 06 '19 at 08:56

2 Answers2

1

There are a few ways to do this without using VBA.

Select the cell that will be used for the condition. Then go to the Conditional Formatting manager and create your condition for the cell itself. In this example, I am highlighting if the cell is not blank.
=NOT(ISBLANK($B$8))

enter image description here

Then click okay and you will be taken back to the rules manager. From there, you can access the "applies to" box for the rule you created. Click the right-corner of the address box and select the entire row for the initially selected cell and then click the right-corner of the address box again to insert the entire row's range in the "applies to" box. Then click Okay. The cell's entire row will now be shaded.

enter image description here

enter image description here

If you prefer to use a named range, you can refer to the cell's entire row there as well. You can either change the formula for "this_cell" to include the entire row, or you can create an additional named range for the row, which is what I've done in this example.

The formula for the named range "this_cell" (note that this formula does not have dollar signs, so you can enter it in other cells too: =Sheet1!B11

The formula for the named range "Shade_this_row": =ROW(this_cell)

enter image description here

After you've done that, create your conditional format rule with the named range. Then click "okay" and make sure the row is in the "apply to" box, click apply and the entire row should be shaded.

enter image description here

Conditional format rule: 

=NOT(ISBLANK(Shade_this_row))

enter image description here

Jenn
  • 612
  • 1
  • 4
  • 7
  • thank you for your detail description, I tried as what you posted, however what if I want to highlight the whole row with the row number of the cell I select each time I select a new cell? but not a specified row? – Woods Chen Jun 06 '19 at 07:02
  • @WoodsChen, Please start the description of your question as follows: "Each time I select a cell, I want to highlight the whole row with the row number of the cell I selected." if that is what you want. Also correct a few spelling errors, please: "created a name base on current cell" should be "create a name based on the current cell" – www.admiraalit.nl Jun 06 '19 at 07:53
0

You can use the named range. Just update the "applies to" section in the Conditional Formatting Rules Manager [Conditional Manager > Manage Rules...]

Xlsx
  • 165
  • 1
  • 12
  • I dont't know if I understand what you mean or not, however if I set `applies to` to the name `current_cell`, the whole sheet will be highlighted. – Woods Chen Jun 06 '19 at 02:47
  • Try amending the "Refers to:" section of the name manager. It should contain locks ($ signs). instead of =A1 try =$A$1 – Xlsx Jun 06 '19 at 07:34
  • Sorry, please ignore the above. The conditional formatting forces a lock, hence it does not allow it to be dynamic, as and when you select the cell – Xlsx Jun 06 '19 at 07:40