color formatting ranges

[Guest]

Hello
I have a macro that uses the Address property but right now its going up to
range (a1:k1) copying the formatting (yellow color) and pasting it into my
activecell.address. Below is what ive been using:
Sub Add_Truck_5()
Dim S
Selection.EntireRow.Insert
S = ActiveCell.Address
Application.Goto Reference:="R1C1"
Range("A1:K1").Select
Selection.Copy
Application.Goto Reference:=ActiveSheet.Range(S), Scroll:=False
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

End Sub
(A1:K1 is formatted to yellow color with some text in A1)

What I want to do now is ignore copying from (a1:k1) but use the same
principal that what ever row I place my cursor in Column A
(activecell.address) is to paint over to column K in that row and color it.

He4Giv (Dick)

 

[Bob Phillips]

Dick,

Are you trying to highlight those cells. if so, this code will highlight the
active row, and clear it on moving on.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
If Target.Column = 1 Then
With Target.Resize(1, 11)
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[Alex J]

Bob,
That works nicely, but seems to clear any conditional formatting on the
sheet.
Is there a way to preserve conditional formatting schemes while using this
approach?

Alex J

 

[Guest]

Bob:
This works great but when it hilites the row over to column K, for example,
I want the formatting to remain and not clear it as I move on to other cells
to work.

 

[Guest]

Bob
I figured out how to get the formatting to remain and it may also keep
conditional formatting is by deleting the first line of code:
"cells.formatconditions.delete"
where ever I place my cursor under column A it adds and retains your
formatting called out in your code eve after i move on to different cells.
Question?
How do i get this macro name to appear in the macro box under tools pull
down>macros?
I want to assign this macro to a custom button and put an icon on the
toolbar or either use the form button where you can assign a macro to the
button on the worksheet.
thanks
Dick