Highlight row and column

[proficient]

Hi all, I need to know that is it possible; if I click anywhere in a sheet it will highlight the whole row and column...?

 

[Raustin]

Glad you found this. I would have never come up with this.

Unless I have misunderstood the question, a much simpler solution can be found with conditional formatting and VBA (and much quicker responding, less memory as well)
Perhaps try this:
Pick two unused cells in your worksheet. Assign one of them to be the selected Row, the other the selected Column. For our purposes we will use A1 as the selected Row and A2 as the selected Column.
In your VBA Editor for the specified sheet enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Range("A1").Value = Target.Row
Range("A2").Value = Target.Column
End Sub

Leave the VBA Editor and back on same worksheet highlight every cell in the sheet (click on upper left box)
Then select your Conditional Formatting, and click on New Rule
Select the "Use a formula to determine which cells to format"
Enter "=$A$1=ROW()"
Then select the formatting you would like (Fill in your case to whatever color you want) You may even like to bold the text as well.
Next click OK and add another new rule for the column, with the formula "=$A$2=COLUMN()" and also assign the formatting as you like
Save all and click OK and you are done
The Result:
Every column and row is highlighted according to the colors you have assigned, with very little code, and colors and formatting can be changed easily. This also works well for protected sheets in which formatting cannot easily be changed.
Please try this and let me know your feedback.

 

[Raustin]

Correction on the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Target.Row
Range("A2").Value = Target.Column
End Sub

 

[Raustin]

oh and another benefit, All existing formatting is preserved.

 

[Peter_SSs]

Please try this and let me know your feedback.

Given that Conditional formatting is volatile and it is being applied to greater than 1,000,000 x 16,000 cells, I was quite sceptical of the performance of this suggestion, especially if the sheet had quite a lot of formulas that might cause a frequent recalculation.

However, with limited testing, I think the performance is good, and considerably more responsive than my suggestion in post #13, though it does behave in a slightly different way.

I'll be interested to hear any other feedback.

 

[Raustin]

Given that Conditional formatting is volatile and it is being applied to greater than 1,000,000 x 16,000 cells, I was quite sceptical of the performance of this suggestion, especially if the sheet had quite a lot of formulas that might cause a frequent recalculation.

However, with limited testing, I think the performance is good, and considerably more responsive than my suggestion in post #13, though it does behave in a slightly different way.

I'll be interested to hear any other feedback.

While the original poster said All Columns and Rows, i am sure he meant just the range he was working in, even if its large. I would recommend limiting the range to just the ranges with values, instead of all rows and all columns, although the code works fine even with all of them.

 

[My Aswer Is This]

The original poster never mentioned being concerned about over writing existing cell interior coloring. I would think using conditional formatting for more then a million cells is excessive. Unless I'm still confused my simple script in post #20 should work I would think. But there are a lot more folks here smarter then me.

 

[Michael M]

I would have thought that generally ANY code that highlights rows / columns will overwrite ANY CF

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim icolor As Integer
icolor = Target.Interior.ColorIndex
icolor = 36
Cells.ClearFormats
ActiveCell.EntireRow.Interior.ColorIndex = icolor
ActiveCell.EntireColumn.Interior.ColorIndex = icolor
End Sub

 

[Raustin]

I would have thought that generally ANY code that highlights rows / columns will overwrite ANY CF

Actually Conditional Formatting supersedes and manual or VBA created formatting so using CF is much better in this case. (since the formatting is only temporary, which is exactly what the Poster had requested)

 

[Peter_SSs]

The original poster never mentioned being concerned about over writing existing cell interior coloring.

Quite true, but that shouldn't stop us making other suggestions that may (or may not) be more appealing to the OP, or other people reading the thread. Just as it doesn't stop you making your suggestion in post #20 after the OP had already said they were quite happy with the code in post #10.

I think you have participated in enough threads in the forum to understand that OPs don't always give all the details up front and that sometimes suggestions made cause them to realise there are other considerations that they hadn't even thought of.

Unless I'm still confused my simple script in post #20 should work I would think.

Nobody has suggested that it doesn't work.

I would think using conditional formatting for more then a million cells is excessive.

It's actually a bit more than that: 17,179,869,184 cells.
But if conditionally formatting that many is excessive, would not directly formatting that many, as your code does, also be excessive?

I, too, initially thought the CF excessive, but on testing with several sheets, some with lots of data and formulas I found it very responsive. In fact, I can't visibly discern a difference between Raustin's code and yours.
Given that Raustin's does preserve any existing formatting, I think it has some definite advantages.

Anyway, the OP seems to be happy with what they have, but there are plenty of options available should they be wanted in the future.

 

[Raustin]

All of your suggestions were great. I use something very similar quite often so this thread has provided a couple of great options.
I just wanted to provide one additional one and did not want to sound condescending. Sorry if I came across that way.
I think another person having the same issues will be able to look through this thread and find a solution that works best for them.
Your input to this site is invaluable.
Thank you.