For Each

[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]

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]

Martin,

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

 

[JS]

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

 

[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

 

[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

 

[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]

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