Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
hi, change the name of the macro from gap to something else because Gap is a
reserved word |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|