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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.