proficient
Well-known Member
- Joined
- Apr 10, 2012
- Messages
- 745
- Office Version
- 2016
- Platform
- Windows
- Mobile
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...?
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)Glad you found this. I would have never come up with this.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Range("A1").Value = Target.Row
Range("A2").Value = Target.Column
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Target.Row
Range("A2").Value = Target.Column
End Sub
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.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.
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
I would have thought that generally ANY code that highlights rows / columns will overwrite ANY CF
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.The original poster never mentioned being concerned about over writing existing cell interior coloring.
Nobody has suggested that it doesn't work.Unless I'm still confused my simple script in post #20 should work I would think.
It's actually a bit more than that: 17,179,869,184 cells.I would think using conditional formatting for more then a million cells is excessive.