Hi acw
I have been doing a bit of playing with yours and JS'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 acw
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 JS's solution below

Ta,
Martin
"acw" wrote in message
...
Martin

Try

Public Sub Gap()
For Each HC In Range("K9:K31")
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
Next
End Sub

Tony
-----Original Message-----
xl2000
I am trying to get the code below to perform an operation
on each cell in
the range("K9:K31").
Currently it only looks at the cells K9, K10 and any
blank cells in the
range. It then changes the color of the border
regardless of whether or not
the cell's value is valid.
Any help would be greatly appreciated.
Ta,
Martin
Public Sub Gap()
Dim HC As Range
Dim HD As Range
Set HC = Range("K11")
Set HD = Range("K12")
For Each HC In Range("K9:K31")
If HC.Value <= (0.7 * HD.Value) Then
With HC.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 26
End With
End If
Next
End Sub


.