Solved: When cell is selected need to change color/highlight the row and columns

[blastpwr1970]

Hi,

Does anybody knows how to change the color/highlight the Row and Column so they intercept on the cell selected and it need to do this every time the cell is selected and remove the color every time the cell is unselected and so on.

Basically I am looking/referencing cell form another sheet or within the same sheet and I need to know the Column and Row I'm looking at because the row and the column have a title and don't want to loose my place plus is lot easier on the eyes due to text being very small so that way I can look at the whole sheet at once.


Thank you
Julio

[Ken Puls]

Hi Julio,

Directly from MS's Site, using conditional formatting. If you have conditional formats on the sheet, you won't want to use this. It won't interfere with any other colour fills though:

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer

'// Note: Don't use if you have conditional
'// formatting 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 in case 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[/vba]

Alternately, if you do use conditional formats and don't want to lose them, the following will not wipe conditional formats, but will wipe any other cell fill colours you have on the sheet:

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Target.Parent
.Cells.Interior.ColorIndex = 0
.Columns(Target.Column).Cells.Interior.ColorIndex = 35
.Rows(Target.Row).Cells.Interior.ColorIndex = 35
End With

End Sub[/vba]

Each of these goes in the code module for the sheet you want it to work on. To get there, just right click the sheet tab and choose View Code.

HTH,

[blastpwr1970]

Thank you Ken, It work's great.

It's unbelievable how fast all of you respond, many thank's to all, great place to be a member.

I do have conditional formatting on the page and many other cells are color coded, but it does't matter I will convert all color coded to conditional formatting and just use the second option.

Thank for the warnings
Julio

[Ken Puls]

Thank you Ken, It work's great.

I do have conditional formatting on the page and many other cells are color coded, but it does't matter I will convert all color coded to conditional formatting and just use the second option.

No problem. Glad to help!

It's unbelievable how fast all of you respond, many thank's to all, great place to be a member.

What, are you kidding? It took almost two hours to get your response... we usually do better than that here.

Cheers!

[blastpwr1970]

Well, I didn't notice the two hours because I post it before I got out of work,eat at home and it was there. "Amazing"

I know I mark it has solved but there is always a but don't you hate that.

I was testing it and found out that when I copy a cell, I can not paste it anymore, this one is a good but it work's great if you don't have to copy paste the content of the cell.

Thank's
Julio

[Ken Puls]

Well now ain't that strange...

I actually downloaded the Excel 2007 Beta 2 and wrote that code in that version. In Beta 2, it works without issue, but in 2003 it cancels the copy. Strange...

At any rate, I've worked up an alternative for you. The thing is that to get it to color band on pasting, I had to limit you to a single copy/paste. i.e. you can't copy/paste/paste/paste... you follow?

Try this... all the code goes in the worksheet module:

[vba]Private Sub Worksheet_Change(ByVal target As Range)
If Application.CutCopyMode <> False Then
Application.CutCopyMode = False
Call ColorBand(target)
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Application.CutCopyMode = False Then
Call ColorBand(target)
Else
Exit Sub
End If
End Sub

Private Sub ColorBand(ByVal rngTarget As Range)
With rngTarget.Parent
.Cells.Interior.ColorIndex = 0
.Columns(rngTarget.Column).Cells.Interior.ColorIndex = 35
.Rows(rngTarget.Row).Cells.Interior.ColorIndex = 35
End With
End Sub[/vba]

[blastpwr1970]

That will do, just one little limitation. Not everything is always perfect but it sure get's close.

Thank you again
Julio

[blastpwr1970]

Now, is there a way to activated it and disactivated with button or checkmark on the sheet?
I now is pushing it.
Thank you
Julio

[Ken Puls]

I now is pushing it.

Nah...

Okay, try this. Add a checkbox to your worksheet from the Control Toolbox (not the forms bar), then update all the code with this:

[vba]Private Sub CheckBox1_Change()
If Me.CheckBox1.Value = False Then
Me.Cells.Interior.ColorIndex = 0
End If
End Sub

Private Sub Worksheet_Change(ByVal target As Range)
If Me.CheckBox1.Value = True Then
If Application.CutCopyMode <> False Then
Application.CutCopyMode = False
Call ColorBand(target)
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If Me.CheckBox1.Value = True Then
If Application.CutCopyMode = False Then
Call ColorBand(target)
Else
Exit Sub
End If
End If
End Sub

Private Sub ColorBand(ByVal rngTarget As Range)
With rngTarget.Parent
.Cells.Interior.ColorIndex = 0
.Columns(rngTarget.Column).Cells.Interior.ColorIndex = 35
.Rows(rngTarget.Row).Cells.Interior.ColorIndex = 35
End With
End Sub[/vba]

Make sure you're not in design mode (click the little triangle on the Control Toolbox to turn it on/off), then check the checkbox. Try moving around the sheet with it turned on/off.

Cheers,

[mdmackillop]

You might be interested in this KB item
http://vbaexpress.com/kb/getarticle.php?kb_id=835

[Ken Puls]

Malcolm, Andrew's entry would wipe any existing conditional formats, wouldn't it? Haven't tested it myself, but that was a concern of Julio's.

[blastpwr1970]

Thank you both,

I tried it and I works great, and yes Ken you are right it will erase the conditional formatting but only temporary if you add a 3rd condition, I do have conditional formatting on my sheet already, I just added the other two and it work.

both of your solutions are great.

The only thing is that it may or may not be a problem when printing it will print the highlighted.
unless you have one cell that does not have the conditional formating.

Is there anything you guys don't know?

Thank you again
Julio