Highlight row and column of selected cell

[SwissExcel]

Hi there,
I am trying to use conditional formatting to highlight the row and column of a selected cell.
is there a way to do it without a macro?

cheers

[6StringJazzer]

No. There is no built-in way to give you the address of the currently selected cell. If you do want a macro, I can help with that.

[SwissExcel]

Thanks for quick replay. Yes - I would appreciate a macro help (even tough I am not very familar

[6StringJazzer]

This method is not the only way to do it but it's one I came up with fairly quickly. It makes minimal use of macros, and uses conditional formatting to actually format the cells.

This solution works only for one specific worksheet.

It detects whenever the selection changes. It uses a hidden sheet Selection Address to store the address of the current selection. The conditional formatting rules refer to this address to determine when formatting applies.

In this example, CF has been applied to A1:M20. Theoretically you could apply it to the entire sheet although many people have reported performance degradation when applying CF to large numbers of cells.

[SwissExcel]

This is very helpful. Thank you for support.
I am trying to run this type of highlighting on several worksheets. is there a way to overcome the supporting sheet (again- I am not such a macro person...)

Cheers. Swiss Excel

[sanram]

If you want a macro solution then this is possible. See the attachment.

[6StringJazzer]

If you want a macro solution then this is possible. See the attachment.

This is effective for multiple sheets. However, please note that this method uses permanent formatting, then when the cursor moves removes that formatting. Therefore, if your sheet has other fill colors on it to start with, they will be lost if you select cells in the same row or column. That is the reason I used conditional formatting to implement my version. If this is not an issue for you then this is a good solution.

I will take a look tomorrow and see what options are available for extending my solution for multiple sheets.

[SwissExcel]

Sanram - thank you for sending the file. this is close to what I am looking (it does change the entire colors of my table so this is not the ideal), but I can somehow live with it...
Question - I would like to copy paste your formula into my own sheet. How do I do that? I have the developer tab, but I wasnt sure where to insert the vba formula and what to do next...

(I am far from being VBA expert)

Cheers

[sanram]

Click on View code at developers tab. Then double click on ThisWorkbook on Project Panel at Left. Now you will see all the codes there. Copy all the codes and paste it at the same location of your main file.