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

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

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