[Martin Wheeler]

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

[JS[_4_]]

hi Martin - not exactly sure whether code below is what you want but by
defining cell as range and looping through each cell in Range("K9:K31") you
will loop through the whole range. If this isn't what you need send me some
more details on the criteria for the loop.

Public Sub Gap()
Dim cell As Range
Dim HC As Range
Dim HD As Range
Set HC = Range("K11")
Set HD = Range("K12")
For Each cell In Range("K9:K31")
If cell.Value < "" And cell.Value <= (0.7 * HD.Value) Then
With cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 26
End With
End If
Next cell
End Sub

[Bob Phillips[_5_]]

Martin,

I don't understand, this seems to work for me. What values do you have in
K9:K31.

--

HTH

Bob Phillips

"Martin Wheeler" wrote in 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

[JS[_4_]]

hi, change the name of the macro from gap to something else because Gap is a
reserved word

[Martin Wheeler]

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

[Martin Wheeler]

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


.

[Martin Wheeler]

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


.

[Tom Ogilvy]

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

[Martin Wheeler]

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