Need expert advice on this. I want to create a macro to draw
separator line between category using conditional formatting (CF). I
a simple example below, I want to create a separator (bottom) line a
A2, A3 and A4 i.e to separate Item X, Y and Z. E.g. the conditiona
formatting formula for

In cell A2, CF formula: if $A2<$A3, then draw bottom line.
In cell A3, CF formula: if $A3<$A4, then draw bottom line.
and so on...

A
1 X
2 X
3 Y
4 Z


Tried with the macro below but always have error (Error 5: Invali
Procedure or Call) in the statement:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$"
ActiveCell.Value & "<$" & NextCell.Value

Thanks.


---------
Sub CreateDownBorder()
' If the current cell value not equat to the next cell/row value
create a separator.
Set NextCell = ActiveCell.Offset(1, 0)
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$"
ActiveCell.Value & "<$" & NextCell.Value
'
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 5
End With
'
End Sub
:

--
Message posted from http://www.ExcelForum.com

slc,

You may not need a macro, just conditional formatting. And you may not need
expert advice either. If this works:

Select your cells. Format - Conditional Formatting - Formula is:

=$A2<$A3

Then select Format - Border, and specify a bottom border.

This is for where the active (white) cell of the selection is A2, and it's
the stuff in column A that will be used to break (cause a border when it
changes). Change accordingly.



--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"slc " wrote in message
...
Need expert advice on this. I want to create a macro to draw a
separator line between category using conditional formatting (CF). In
a simple example below, I want to create a separator (bottom) line at
A2, A3 and A4 i.e to separate Item X, Y and Z. E.g. the conditional
formatting formula for

In cell A2, CF formula: if $A2<$A3, then draw bottom line.
In cell A3, CF formula: if $A3<$A4, then draw bottom line.
and so on...

A
1 X
2 X
3 Y
4 Z


Tried with the macro below but always have error (Error 5: Invalid
Procedure or Call) in the statement:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$" &
ActiveCell.Value & "<$" & NextCell.Value

Thanks.


---------
Sub CreateDownBorder()
' If the current cell value not equat to the next cell/row value,
create a separator.
Set NextCell = ActiveCell.Offset(1, 0)
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$" &
ActiveCell.Value & "<$" & NextCell.Value
'
With Selection.FormatConditions(1).Borders(xlBottom)
LineStyle = xlContinuous
Weight = xlThick
ColorIndex = 5
End With
'
End Sub
:)


---
Message posted from http://www.ExcelForum.com/

Earl,

Thanks for your note. The reason I want it to me in macro because
will re-use this with a lot of data and many times. Anyway, I manag
to solve it with the following procedure. Am posting it for th
benefit of others.


Sub CreateDownBorder()
'
'
Dim Str As String
Set NextCell = ActiveCell.Offset(1, 0)
Selection.FormatConditions.Delete
Str = "=" & ActiveCell.Address(RowAbsolute:=False) & "<"
NextCell.Address(RowAbsolute:=False)
MsgBox Str
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=Str
'
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End Su

--
Message posted from http://www.ExcelForum.com

slc,

I replied to this last night, but it doesn't show up today. Here it is
again from my Sent Items folder. Wonder if it'll show up twice eventually.

You may not need a macro, just conditional formatting. And you may not need
expert advice either. If this works:

Select your cells. Format - Conditional Formatting - Formula is:

=$A2<$A3

Then select Format - Border, and specify a bottom border.

This is for where the active (white) cell of the selection is A2, and it's
the stuff in column A that will be used to break (cause a border when it
changes). Change accordingly.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"slc " wrote in message
...
Need expert advice on this. I want to create a macro to draw a
separator line between category using conditional formatting (CF). In
a simple example below, I want to create a separator (bottom) line at
A2, A3 and A4 i.e to separate Item X, Y and Z. E.g. the conditional
formatting formula for

In cell A2, CF formula: if $A2<$A3, then draw bottom line.
In cell A3, CF formula: if $A3<$A4, then draw bottom line.
and so on...

A
1 X
2 X
3 Y
4 Z


Tried with the macro below but always have error (Error 5: Invalid
Procedure or Call) in the statement:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$" &
ActiveCell.Value & "<$" & NextCell.Value

Thanks.


---------
Sub CreateDownBorder()
' If the current cell value not equat to the next cell/row value,
create a separator.
Set NextCell = ActiveCell.Offset(1, 0)
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$" &
ActiveCell.Value & "<$" & NextCell.Value
'
With Selection.FormatConditions(1).Borders(xlBottom)
LineStyle = xlContinuous
Weight = xlThick
ColorIndex = 5
End With
'
End Sub
:)


---
Message posted from http://www.ExcelForum.com/