Thanks Tom,
As usual your code works great.
Ta,
Martin

"Tom Ogilvy" wrote in message
...
If you are using excel 97

Public Sub Gap()
Dim HC As Range
Dim HD As Range
For Each HC In Range("K9:K31")
If HC.Offset(1, 0) < "" and HC <"" Then
if isnumeric(HC.offset(1,0).value) _
and isnumeric(HC.value) then
If HC.Value <= (0.7 * HC.Offset(1, 0).Value) Then
With HC.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 26
End With
End If
End if
End If
Next
End Sub

Regards,
Tom Ogilvy




Martin Wheeler wrote in message
...
Hi JS
I have been doing a bit of playing with yours and acw's code and now
have
Public Sub Gap()
Dim HC As Range
Dim HD As Range
For Each HC In Range("K9:K31")
If HC.Offset(1, 0) < "" Then
If HC.Value <= (0.7 * HC.Offset(1, 0).Value) Then
With HC.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 26
End With
End If
End If
Next
End Sub
This works but am still getting the type mismatch error
Ta,
Martin
"Martin Wheeler" wrote in message
...
Hi JS
Thanks for your reply.
I am getting a type mismatch error and it is highlighting
If HC.Value <= (0.7 * HC.Offset(1, 0).Value) Then
The same applies to acw's solution below
Ta,
Martin

"JS" wrote in message
...
hi Martin - i see what you mean, try the following code

Sub Gap()

Dim cell As Range

For Each cell In Range("K9:K31")
If cell.Offset(1, 0) < "" Then
If cell.Value (0.7 * (cell.Offset(1, 0).Value)) Then
With cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 26
End With
End If
End If
Next cell

End Sub