Solved: Highlight the entire row on click

[sasa]

I am wondering if exists the chance to highlight the entire row where I click on selected cell. For instance, I select a cell in row 5, I want the entire row to be highlighted. If I then select a cell in Row 8, I want Row 5 to return to normal, and Row 8 to be highlighted.

Thanks for any help

sasa

[dominicb]

Good morning sasa

to highlight the entire row where I click on selected cell.
Something like this will work OK, when placed into the code module of the sheet in question. This will also restore the column you clicked on as the active cell.

Here's the caveat : a routine like this works on the selection change event, which means that the routine is run every time the selection is changed. In time every time the routine is run your undo stack is cleared, therefor using a routine such as this means that you will lose your undo ability. There is no way around this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As String
a = ActiveCell.Address
ActiveCell.EntireRow.Select
Range(a).Activate
End Sub

If the caveat bit doesn't put you off, you might like to have a look at Chip Pearson's Rowliner add-in - it's a more sophisticated way of achieving the result :
http://www.cpearson.com/excel/RowLiner.htm

HTH

DominicB

[Bob Phillips]

One way




'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[JimmyTheHand]

One way

Bob, this one is very pretty :thumb

One thing to add, though. It didn't worked for me until I realized that in this part of the code
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" the expression "TRUE" must be replaced with the local word that corresponds to TRUE. In Hungarian Excel, it looks like this:
.FormatConditions.Add Type:=xlExpression, Formula1:="IGAZ" I'm not sure what language is used in Alderney, but for others, this may be of help.

Jimmy

[marshybid]

Hi xld,

I just pated this code into a worksheet and it works really well.

This is something that I could use for another project I am managing, it would help users to identify the relevand data set.

What would I need to do to the code to embed it into a spreadsheet that I send out to others. Would this then be blocked when they open the sheet??

Thanks,

Marshybid

[Bob Phillips]

Bob, this one is very pretty :thumb

One thing to add, though. It didn't worked for me until I realized that in this part of the code
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" the expression "TRUE" must be replaced with the local word that corresponds to TRUE. In Hungarian Excel, it looks like this:
.FormatConditions.Add Type:=xlExpression, Formula1:="IGAZ" I'm not sure what language is used in Alderney, but for others, this may be of help.

Jimmy

Wasn't aware of this Jimmy. When you add a formula to a cell in VBA, you always use English for the functions, but I guess not for the booleans.

Can you try this for me



ActiveCell.FormulaR1C1 = "=IF(A1=17,TRUE,FALSE)"


I know it is a rfubbish formula, but how does it map in your Excel?

[Bob Phillips]

Hi xld,

I just pated this code into a worksheet and it works really well.

This is something that I could use for another project I am managing, it would help users to identify the relevand data set.

What would I need to do to the code to embed it into a spreadsheet that I send out to others. Would this then be blocked when they open the sheet??

Thanks,

Marshybid

If you just paste it into that workbook, it will just need macros enabling.

Be aware of its one (BIG!!) limitation, it wipes out any conditional formatting on that sheet.

I have a version that is an addin that sets it by sheet, by workbook, and can toggle it on and off.

[marshybid]

Thanks for that xld. Is it possible to see the version that you have as an addin??

Marshybid

[Bob Phillips]

Sure. It might take me a cuple of days to dig out as it is on another machine. I never published it.

[JimmyTheHand]

ActiveCell.FormulaR1C1 = "=IF(A1=17,TRUE,FALSE)"
I know it is a rfubbish formula, but how does it map in your Excel?
Running the above line I got this formula in the active cell:
=HA('A1'=17;IGAZ;HAMIS)
The result was #NAME? error, until I removed the apostrophes around A1.
Don't know how they got there :dunno Nevertheless, the booleans got translated.

I checked the active cell's conditional format (set by your code with the original "TRUE") and the expression was
="TRUE"
So, it didn't get translated here. Strangely, if I write "TRUE" (string) into the cell, it still does nothing.

I don't understand. But won't miss a sleep ovet it :)

Jimmy

EDIT:
I got the formula right. It should have been
ActiveCell.Formula = "=IF(A1=17,TRUE,FALSE)"
(without "R1C1")

[marshybid]

Thanks xld, I'll look forward to seeing it in a few days.

Marshybid

[Bob Phillips]

Running the above line I got this formula in the active cell:
=HA('A1'=17;IGAZ;HAMIS)
The result was #NAME? error, until I removed the apostrophes around A1.
Don't know how they got there :dunno Nevertheless, the booleans got translated.

As you correctly surmised, it was because I used the FormulaR1C1, but added an A1 formula. I guess Excel puts quotes around the A1 because it is being supplid in R1C1 type formula it thinks the A1 is a name, and you can't have a name that is a cell reference.

[sasa]

Hi xld,
You are so great ! The best in the world.
I confirm the necessity to change the expression "TRUE".:cool:
I tried and it works very well. But how I can deactivate the macro ?
sasa

[sasa]

Hi DominicB,
I tried your routine, it works too, and does not wipe out any conditional formatting on that sheet.This is good for me because I have a lot of conditional formatted columns. Is there a way to off your code and on again when I need it ?

Thanks a lot

sasa

[sasa]

Hi All,
what about this code ?
It was good for me.
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static rr
Static cc

If cc <> "" Then
With Columns(cc).Interior
.ColorIndex = xlNone
End With
With Rows(rr).Interior
.ColorIndex = xlNone
End With
End If

r = Selection.Row
c = Selection.Column
rr = r
cc = c

With Columns(c).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With
With Rows(r).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With

It looks like it does not wipe the conditional formatting and it
allows me the cut and copy option.

Thanks again !!!!

sasa
End Sub