Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change entire row colour when row is selected?
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change entire row colour when row is selected?
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 wrote in message oups.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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change entire row colour when row is selected?
"NickHK" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change entire row colour when row is selected?
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 wrote in message oups.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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to change entire row colour when row is selected?
I like this add-in
http://www.cpearson.com/excel/RowLiner.htm -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change selected cell shading colour | Excel Discussion (Misc queries) | |||
how do I change the colour of selected auto filter tabs | Excel Worksheet Functions | |||
selected cells doesnot change colour | New Users to Excel | |||
change highlight colour of selected cells | Excel Discussion (Misc queries) | |||
how to colour the entire row with the selected word | Excel Discussion (Misc queries) |