Highlighting column and row intersection cells?

[gaftalik]

Hi everybody,

Any code or formula to highlight the intersected cells between a column and a row for any selected cell ?

Ex: if i select E25 the cells of column E1:E25 as well as the cells of Row A25:E25 to be highlighted in a certain color , and so on for any selected cell..

Thank you much for your help .

 

[gaftalik]

I am trying to format cells with colors and they are disappearing after i move my mouse selection, could i do anything about this ?

I am much grateful for your support!

 

[TheNoocH]

i was afraid of that...i just cleared all the colors from the range...

will you be using a color for the row/col highlighting that is different than any other color? if so i'll just reset that color?

 

[gaftalik]

Exactly, it's a large table with headers and row titles for which i have to format each separately, could we just let it highlight if cells are not color formatted ? by other means to ignore colored cells?

 

[TheNoocH]

give this the ole college try...
Note change clrind=42 to whatever colorindex you are using...right now i changed it so you can only use a colorindex and not color constants....

Private Sub Worksheet_SelectionChange(ByVal target As Range)
 If target.Cells.Count > 1 Then Exit Sub
   Set Rng = Range("A1:Z100")
   clrind = 42
   If Not Intersect(target, Rng) Is Nothing Then
   For Each cell In Rng
        If cell.Interior.ColorIndex = clrind Then cell.Interior.Pattern = xlNone
   Next cell
   For Each cell In Range(Cells(1, target.Column), Cells(target.Row, target.Column))
      If cell.Interior.Pattern = xlNone Then cell.Interior.ColorIndex = clrind
   Next cell
   For Each cell In Range(Cells(target.Row, 1), Cells(target.Row, target.Column))
      If cell.Interior.Pattern = xlNone Then cell.Interior.ColorIndex = clrind
   Next cell
  End If
End Sub

 

[gaftalik]

Thats it, but going too slow ?!! is it supposed to be cause i made the range bigger !

 

[TheNoocH]

how big did you make the range? it could part of the issue b/c it's looping through that whole range to uncolor any previous row/col coloring...

 

[gaftalik]

i made it to A1: GA400

 

[TheNoocH]

i just changed my range to the same and it is quite slow...let me see if i can figure out a better way than looping all the cells...

 

[gaftalik]

Hope i am not buzzing, i was trying to copy a range of cells values and paste it in another row and that was impossible, seems it prevent other formatting operations.

 

[TheNoocH]

not sure i'm following...can you give more details?