Temporarily change formatting?

[RogerC]

I would like to be able to use Dave Hawley's code below to temporarily highlight the active row. The problem is I have a lot of Conditional Formatting in my worksheet. When the code runs in the Sheet it clears all of the existing Conditional Formatting. Can anyone think of a way to make the code effect only the active row and to 'replace' the pre-exitsting Conditional Formatting when that row is no longer the active row?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete


With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTA(" & strRow & ")>0"

.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 15
End With
End Sub

Thanks!

 

[Ian Mac]

On 2002-09-05 10:58, RogerC wrote:
I would like to be able to use Dave Hawley's code below to temporarily highlight the active row. The problem is I have a lot of Conditional Formatting in my worksheet. When the code runs in the Sheet it clears all of the existing Conditional Formatting. Can anyone think of a way to make the code effect only the active row and to 'replace' the pre-exitsting Conditional Formatting when that row is no longer the active row?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strRow As String
Cells.FormatConditions.Delete


With Target.EntireRow
strRow = .Address
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTA(" & strRow & ")>0"

.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 15
End With
End Sub

Thanks!

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Rows(dRow).Interior.ColorIndex = xlNone
Columns(dCol).Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 36
Columns(Target.Column).Interior.ColorIndex = 36
dRow = Target.Row
dCol = Target.Column
End Sub


This will do both Column and Row, take out the bold parts if you don't need the row.
I haven't tested this with Conditional Formatting on a sheet, so I don't know if it's what you require.

 

[Joe Was]

Try this, it will color the selected row yellow. As any row is selected the old row is uncolored. All conditional formatting is active, before, durring and after the selection. Once you select a row, from any cell in that row, you can activate a cell in that row with the Enter key (Set edit option to move right.), the mouse will activate cell "A1." You could change the code to work on a condition or test as well. JSW

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = -4142
With Target.Interior
Target.EntireRow.Select
.ColorIndex = 6
End With
End Sub

 

[Joe Was]

Ian's code can be reset to only highlight the active selection, rather than color each and every selection, this way:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Cells.Interior.ColorIndex = -4142
Rows(dRow).Interior.ColorIndex = xlNone
Columns(dCol).Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 36
Columns(Target.Column).Interior.ColorIndex = 36
dRow = Target.Row
dCol = Target.Column
End Sub

 

[RogerC]

Thanks Ian. But I think I've realized I won't be able to use both. The Conditional Formatting I'm using colors every other row light blue (for visual distinction). I wanted to only temporarily change the row color (light yellow) while the row is active, and then change it back when the user moves to a different row. Where the rows are already colored by Conditinal Formatting, the code does not hightlight the row.

 

[RogerC]

Thanks Joe, but I have the same problem. I'm using the Formula =MOD(Row(),2)=0 conditionally to shade every other row. On those rows that have shading, the code does not highlight the row. I'm thinking it is not possible to use both techniqes together.

 

[Joe Was]

You may be right, I tested it with Format-Conditional Format: Between Value and Format cell Pattern to light red and it worked for me. This is what I thought you ment by "conditionally formatted?"

 

[RogerC]

Yes Joe, you are right.. I tested it with other conditional formatting and it worked fine also. But with the =MOD(Row(),2)=0 formula it does not. Tthanks though.