Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace XLThin Line with XLHairline
Hi,
I would like to write code that replaces any line found within a range that has a weight property of "XLThin" and replaces it with the weight property of "XLHairline". I don't care which part of the cell (top, bottom, left, right) this "XLThin" line resides as I want to keep the position of this line generic. Any line regardless of where in the spreadsheet, I want change to change it from "XLThin" to "XLHairline". Excel doesn't seem to enable the user to do this as you must pick of location of where this line is relative to a cell. Here is the code that I have: With Application.FindFormat.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With Application.ReplaceFormat.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlHairline End With Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True How do I make this position unspecific? I.E. neither "xlEdgeLeft" nor "xlEdgeTop"? I want to change any thin line with a hair line with simple find/replace VBA code. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace XLThin Line with XLHairline
Can't you just find/replace all 4 options, so first do bottom then left,
then right then top? I suppose you are not interested in inside diagonal etc. RBS "klysell" wrote in message ... Hi, I would like to write code that replaces any line found within a range that has a weight property of "XLThin" and replaces it with the weight property of "XLHairline". I don't care which part of the cell (top, bottom, left, right) this "XLThin" line resides as I want to keep the position of this line generic. Any line regardless of where in the spreadsheet, I want change to change it from "XLThin" to "XLHairline". Excel doesn't seem to enable the user to do this as you must pick of location of where this line is relative to a cell. Here is the code that I have: With Application.FindFormat.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With Application.ReplaceFormat.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlHairline End With Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True How do I make this position unspecific? I.E. neither "xlEdgeLeft" nor "xlEdgeTop"? I want to change any thin line with a hair line with simple find/replace VBA code. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace XLThin Line with XLHairline
Hi RB Smissaert,
Thanks for your input. It's a complicated pivot table that isn't responding to the conventional methods of formatting thus necessitating creative methods to change line thicknesses, etc. Cheers, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 "RB Smissaert" wrote: Can't you just find/replace all 4 options, so first do bottom then left, then right then top? I suppose you are not interested in inside diagonal etc. RBS "klysell" wrote in message ... Hi, I would like to write code that replaces any line found within a range that has a weight property of "XLThin" and replaces it with the weight property of "XLHairline". I don't care which part of the cell (top, bottom, left, right) this "XLThin" line resides as I want to keep the position of this line generic. Any line regardless of where in the spreadsheet, I want change to change it from "XLThin" to "XLHairline". Excel doesn't seem to enable the user to do this as you must pick of location of where this line is relative to a cell. Here is the code that I have: With Application.FindFormat.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With Application.ReplaceFormat.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlHairline End With Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True How do I make this position unspecific? I.E. neither "xlEdgeLeft" nor "xlEdgeTop"? I want to change any thin line with a hair line with simple find/replace VBA code. Thanks, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.948-9557 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace # with new line in a cell. | Excel Discussion (Misc queries) | |||
Find and Replace where there are line breaks | Excel Discussion (Misc queries) | |||
inserting line break with find and replace. | Excel Programming | |||
How can I do a find and replace for a cell that has multiple line. | Excel Discussion (Misc queries) | |||
Adding line breaks by using find and replace | Excel Discussion (Misc queries) |