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 tried to copy the range G72:T72 and paste it in J72:T72 but nothing happened , nothing was pasted .

 

[TheNoocH]

weird...i just tried to copy and paste and didn't have any issues...

 

[TheNoocH]

OK...found this code with the help of "gettingbetter"...it's from a microsoft MVP and seems to do what you want...try it out and let me know....

the code is from these posts
http://www.mrexcel.com/forum/showthread.php?t=14241
http://www.mrexcel.com/hof001.php

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
    iColor = 36
Else
    iColor = iColor + 1
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:="TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:="TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub

 

[gaftalik]

Thank you so much!

 

[erik.van.geit]

While this solutions works, the major drawback is that you do not have UNDO anymore.

You can apply conditional format: I had a thought about the formula to apply in your case, so only the cells "before" will be highlighted.
You can do it with this formula:

'=OR(AND(ROW()=CELL("row"),COLUMN()<=CELL("col")),AND(ROW()<=CELL("row"),COLUMN()=CELL("col")))

still using the code in sheetmodule

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" width=64 height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99" width=64></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow" width=64></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" height=17></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow"></TD><TD class=xl25 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: yellow"> active cell</TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffcc99" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #ffcc99"></TD></TR></TBODY></TABLE>
best regards,
Erik

 

[gaftalik]

You are right ! i cant redo it i was deleting the code each time i wanted to format!

How can i change the color of each cell to one of those 56 colors, i tried format cells> custom>[Color39] and changed the font color not the pattern ! can you help please ?

Thank you much

 

[erik.van.geit]

your last question is not clear to me
are you talking about my last reply?

 

[gaftalik]

What i should write in the custom tab in format cells in order to change the cell color to the color 39 ?

Thank you

 

[TheNoocH]

Erik...nicely done with conditional formatting...

gaftalik...when you set the conditional formatting change the format (via the FILL tab) to any color you like...NOTE: Erik's solution is done via conditional formatting...the only code he uses is to force a screen refresh on a selection change...

 

[gaftalik]

What about if i need some other colors which are not on the fill palette ?