How to change entire row colour when row is selected?

[gregglazar]

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

 

[Zone]

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]

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]

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

 

[Joerg]

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

 

[Peter T]

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

 

[Ron de Bruin]

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