[SOLVED] How to change entire row colour when row is selected?

To anyone who can help,

I am trying to change all the text of a row to red and bold when that
row is selected and then change it back to black and unbold when it is
deselected.

I have achieved the first part of my task with the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row = 4 Then
ActiveCell.EntireRow.Font.ColorIndex = 3
ActiveCell.EntireRow.Font.Bold = True
End If
If ActiveCell.Row = 5 Then
ActiveCell.EntireRow.Font.ColorIndex = 3
ActiveCell.EntireRow.Font.Bold = True
End If
End Sub

Can someone please help with the else statement that I would need to
add so that when I change from say, row 4 to 5, the text in row 4 is
returned to black and unbold while the text in row 5 is changed to red
and bold?

Any help will be much appreciated

How about this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row = 4 Then
Rows(5).EntireRow.Font.ColorIndex = xlAutomatic
Rows(5).EntireRow.Font.Bold = False
ActiveCell.EntireRow.Font.ColorIndex = 3
ActiveCell.EntireRow.Font.Bold = True
End If
If ActiveCell.Row = 5 Then
Rows(4).EntireRow.Font.ColorIndex = xlAutomatic
Rows(4).EntireRow.Font.Bold = False
ActiveCell.EntireRow.Font.ColorIndex = 3
ActiveCell.EntireRow.Font.Bold = True
End If
End Sub

James

gregglazar@gmail.com wrote:
> To anyone who can help,
>
> I am trying to change all the text of a row to red and bold when that
> row is selected and then change it back to black and unbold when it is
> deselected.
>
> I have achieved the first part of my task with the following code:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If ActiveCell.Row = 4 Then
> ActiveCell.EntireRow.Font.ColorIndex = 3
> ActiveCell.EntireRow.Font.Bold = True
> End If
> If ActiveCell.Row = 5 Then
> ActiveCell.EntireRow.Font.ColorIndex = 3
> ActiveCell.EntireRow.Font.Bold = True
> End If
> End Sub
>
> Can someone please help with the else statement that I would need to
> add so that when I change from say, row 4 to 5, the text in row 4 is
> returned to black and unbold while the text in row 5 is changed to red
> and bold?
>
> Any help will be much appreciated

That would certainly work but I need to perform the function for many
rows and many columns, so I want some user friendly code that won't
require to much repetition, which is why I want a use an else
statement.

I bascially need some code so that when row 4 is selected, all text in
row 4 is made bold and red, while the text in ALL other rows is made
black and unbold. The, if row 7 is selected, all the text in row 7 is
made bold and red, while the text in ALL other rows is made black and
unbold.

Does anybody know how to perform this?

Something like this, so only the first row is changed, if you select
multiple rows:

Dim OldRange As Range

Private Sub Worksheet_Activate()
Set OldRange = ActiveCell.Range("A1")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Clear the old formatting
With OldRange.EntireRow.Font
.ColorIndex = xlAutomatic
.Bold = False
End With
'Set the new formatting
With Target.Range("A1").EntireRow.Font
.ColorIndex = 3
.Bold = True
End With
'Reset the range
Set OldRange = Target.Range("A1")
End Sub

NickHK

<gregglazar@gmail.com> wrote in message
news:1154570187.495005.318670@s13g2000cwa.googlegroups.com...
> That would certainly work but I need to perform the function for many
> rows and many columns, so I want some user friendly code that won't
> require to much repetition, which is why I want a use an else
> statement.
>
> I bascially need some code so that when row 4 is selected, all text in
> row 4 is made bold and red, while the text in ALL other rows is made
> black and unbold. The, if row 7 is selected, all the text in row 7 is
> made bold and red, while the text in ALL other rows is made black and
> unbold.
>
> Does anybody know how to perform this?
>

"NickHK" <TungCheWah@Invalid.com> wrote in message
news:%23lG7hrrtGHA.4752@TK2MSFTNGP02.phx.gbl...
> Something like this, so only the first row is changed, if you select
> multiple rows:
>
> Dim OldRange As Range
>
> Private Sub Worksheet_Activate()
> Set OldRange = ActiveCell.Range("A1")
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> 'Clear the old formatting
> With OldRange.EntireRow.Font
> .ColorIndex = xlAutomatic
> .Bold = False
> End With
> 'Set the new formatting
> With Target.Range("A1").EntireRow.Font
> .ColorIndex = 3
> .Bold = True
> End With
> 'Reset the range
> Set OldRange = Target.Range("A1")
> End Sub
>
> NickHK



I changed above solution a bit. The following code "remembers" every
previous selected row and changes fonts back automatically (code goes into
code page of sheet):

Dim Oldaddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Oldaddress = "" Then Oldaddress = ActiveCell.Address 'Initialize
Oldaddress
'Set the new formatting
With ActiveCell.EntireRow.Font
.ColorIndex = 3
.Bold = True
End With
If Range(Oldaddress).EntireRow.Address <> ActiveCell.EntireRow.Address
Then
With Range(Oldaddress).EntireRow.Font
.ColorIndex = xlAutomatic
.Bold = False
End With
End If
Oldaddress = ActiveCell.Address
End Sub

Cheers,
Joerg

Another one, which will at least maintain Undo and the clipboard while
selecting other cells in the same row. But select a cell in a different
column and both lost. Also not necessary to maintain a reference to the
previously formatted row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim v As Variant

With Target(1).EntireRow.Font
v = .ColorIndex
If v <> 3 Or IsNull(v) Then
With Cells.Font
.ColorIndex = xlAutomatic
.Bold = False
End With
.ColorIndex = 3
.Bold = True
End If
End With

End Sub

To maintain the clipboard while selecting in a different column bracket the
code with
If Application.CutCopyMode = 0 Then
'code
end if

Obviously with this method not possible to maintain colour & bold formats,
but there are other ways of highlighting selected row.

Regards,
Peter T

<gregglazar@gmail.com> wrote in message
news:1154570187.495005.318670@s13g2000cwa.googlegroups.com...
> That would certainly work but I need to perform the function for many
> rows and many columns, so I want some user friendly code that won't
> require to much repetition, which is why I want a use an else
> statement.
>
> I bascially need some code so that when row 4 is selected, all text in
> row 4 is made bold and red, while the text in ALL other rows is made
> black and unbold. The, if row 7 is selected, all the text in row 7 is
> made bold and red, while the text in ALL other rows is made black and
> unbold.
>
> Does anybody know how to perform this?
>

I like this add-in
http://www.cpearson.com/excel/RowLiner.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



<gregglazar@gmail.com> wrote in message news:1154565059.377956.279080@p79g2000cwp.googlegroups.com...
> To anyone who can help,
>
> I am trying to change all the text of a row to red and bold when that
> row is selected and then change it back to black and unbold when it is
> deselected.
>
> I have achieved the first part of my task with the following code:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If ActiveCell.Row = 4 Then
> ActiveCell.EntireRow.Font.ColorIndex = 3
> ActiveCell.EntireRow.Font.Bold = True
> End If
> If ActiveCell.Row = 5 Then
> ActiveCell.EntireRow.Font.ColorIndex = 3
> ActiveCell.EntireRow.Font.Bold = True
> End If
> End Sub
>
> Can someone please help with the else statement that I would need to
> add so that when I change from say, row 4 to 5, the text in row 4 is
> returned to black and unbold while the text in row 5 is changed to red
> and bold?
>
> Any help will be much appreciated
>