For Each

  • Thread starter Thread starter Martin Wheeler
  • Start date Start date
M

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
 
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
 
Martin,

I don't understand, this seems to work for me. What values do you have in
K9:K31.
 
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
 
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
 
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
 
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
 
Back
Top