Highlight Code

[Papi]

Below is some code that I have been using for some time. The way it is written is that it sends the cursor to column A each time the code is ran. What can be done so that when the code is ran that the cursor remains where it is?

Option Explicit

Dim Rng As Range

Sub HighlightYellow()

Range("A" & ActiveCell.Row).Select

Set Rng = ActiveCell.Range("A1:Z1")

With Rng.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Application.OnTime Now + TimeValue("00:00:10"), "RemoveHighlight"

End Sub

Sub RemoveHighlight()

With Rng.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub

 

[Marcelo Branco]

Maybe this

Option Explicit
Dim rng As Range
 
Sub HighlightYellow()
    
    Set rng = Range("A" & ActiveCell.Row & ":Z" & ActiveCell.Row)
 
    With rng.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    Application.OnTime Now + TimeValue("00:00:10"), "RemoveHighlight"
 
End Sub
 
Sub RemoveHighlight()
    
    With rng.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
End Sub

HTH

M.

 

[shg]

Try changing the first sub to

Sub HighlightYellow()
    Set Rng = ActiveCell.EntireRow.Range("A1:Z1")
 
    With Rng.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    Application.OnTime Now + TimeValue("00:00:10"), "RemoveHighlight"
End Sub

 

[Papi]

Thanks very much to both Marcelo Branco and shg as both solutions work fine.

One further question, I have two conditions set in conditional formatting and they override this code. Is there some way that the highlight macro can override the two conditions and highlight instead?

 

[shg]

You would have to delete the conditional formatting and then reapply it.

 

[Papi]

Thanks shg. Would that be possible by adding that to the highlight code to remove current conditions for only that row, apply the highlight and when done re-apply the condition in the same code?

 

[Marcelo Branco]

Thanks very much to both Marcelo Branco and shg as both solutions work fine.

One further question, I have two conditions set in conditional formatting and they override this code. Is there some way that the highlight macro can override the two conditions and highlight instead?

You are welcome.

shg has already answered your question.

M.

 

[shg]

Deleting the conditional formatting is easy.

To restore it, you need to reapply it in code, or copy it from somewhere else.

Why not just apply some formatting that isn't use by the conditional formatting, like borders?

 

[Papi]

Thanks again. I will attempt to turn the condition off and back on. The worst case scenario is that it does not work. Running something on a copy of such a file just means I will not disapoint myself when I mess it up.

Thanks to both of you for all your help.