color formatting ranges
 

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)

color formatting ranges
 

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.


--
HTH

-------

Bob Phillips
"He4Giv" wrote in message
...
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)

color formatting ranges
 

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

"Bob Phillips" wrote in message
...
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.


--
HTH

-------

Bob Phillips
"He4Giv" wrote in message
...
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)

color formatting ranges
 

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.

"Bob Phillips" wrote:

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.


--
HTH

-------

Bob Phillips
"He4Giv" wrote in message
...
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)

color formatting ranges
 

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
downmacros?
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

"Alex J" wrote:

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

"Bob Phillips" wrote in message
...
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.


--
HTH

-------

Bob Phillips
"He4Giv" wrote in message
...
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)