Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting ranges in VBA | Excel Discussion (Misc queries) | |||
highlight color missing when selecting cell ranges with CTRL key | Excel Discussion (Misc queries) | |||
Filtering Data in ranges and changing duplicate cells to a color | Setting up and Configuration of Excel | |||
Conditional color formatting entries have wild color. | Excel Discussion (Misc queries) | |||
Ranges within Conditional Formatting | Excel Discussion (Misc queries) |