Temporarily change formatting?

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Mar 25, 2002
Messages
533

ADVERTISEMENT

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

Well-known Member
Joined
Mar 25, 2002
Messages
533
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Mar 25, 2002
Messages
533
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.
 

Forum statistics

Threads
1,143,920
Messages
5,721,541
Members
422,369
Latest member
redinator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top