PDA

View Full Version : Extend the code up to row 21



jammer6_9
03-13-2011, 12:45 AM
Below code give what I require to row 4 only but how can I extend the code until row 21


Sub ColorMyCell()

Application.ScreenUpdating = False
On Error Resume Next

If Range("h4") = "Completed" Then
Range("j4:M4").Interior.ColorIndex = 1
Range("j4:M4").Value = ""
Else
Range("j4:M4").Interior.ColorIndex = 2



If Range("j4") = "Completed" Then
Range("l4:M4").Interior.ColorIndex = 1
Range("l4:M4").Value = ""
Else
Range("l4:M4").Interior.ColorIndex = 2

End If
End If

End Sub

JimmyTheHand
03-13-2011, 02:34 AM
Not tested, but should work.
Sub ColorMyCell()
Dim i As Long
Application.ScreenUpdating = False
On Error Resume Next
For i=4 To 21
If Range("h" & i) = "Completed" Then
Range("j" & i).Resize(,4).Interior.ColorIndex = 1
Range("j" & i).Resize(,4).Value = ""
Else
Range("j" & i).Resize(,4).Interior.ColorIndex = 2
If Range("j" & i) = "Completed" Then
Range("l" & i).Resize( ,2).Interior.ColorIndex = 1
Range("l" & i).Resize( ,2).Value = ""
Else
Range("l" & i).Resize( ,2).Interior.ColorIndex = 2

End If
End If
Next

Application.ScreenUpdating = True
End Sub
Jimmy

jammer6_9
03-13-2011, 05:10 AM
It works but why it take time to process the code?


Not tested, but should work.
Sub ColorMyCell()
Dim i As Long
Application.ScreenUpdating = False
On Error Resume Next
For i=4 To 21
If Range("h" & i) = "Completed" Then
Range("j" & i).Resize(,4).Interior.ColorIndex = 1
Range("j" & i).Resize(,4).Value = ""
Else
Range("j" & i).Resize(,4).Interior.ColorIndex = 2
If Range("j" & i) = "Completed" Then
Range("l" & i).Resize( ,2).Interior.ColorIndex = 1
Range("l" & i).Resize( ,2).Value = ""
Else
Range("l" & i).Resize( ,2).Interior.ColorIndex = 2

End If
End If
Next

Application.ScreenUpdating = True
End Sub
Jimmy

JimmyTheHand
03-13-2011, 05:30 AM
What do you mean? Every code takes time to process.
When I ran the code it finished in a fragment of a second.

mdmackillop
03-13-2011, 07:01 AM
It works but why it take time to process the code?
The code as written should not take long to execute, but if the sheet is recalculating due to the changes, that can take a considerable time.
Try setting Calculation to Manual and then resetting to Automatic on completion.

jammer6_9
03-14-2011, 12:12 PM
md Yes it does calculate during changes... i tried you suggestion and works fine thanks. hats off to you jimmy!