The code below works well also. However, I got 2 problems here.
First, I manage to find the word " Sunday", then color the cell below that Sunday. However, I need Excel auto find out Sunday and color that particular columns for me, for example, Sunday found and color start from cell(L55) below that Sunday up to more cells(L59), Range("L55:L59").
Second, I only manage to find first Sunday, I wish the system keep find and keep color untill it found that rows blank, so I using this code.
'Encountered blank cell in row 2, terminate search
If Len(Cells(2, LColumn)) = 0 Then
MsgBox "No matching date was found."
Exit Sub
I attached the excel files with sample, I really hope someone can help, these problems delay me 2 months d. Thanks alot !
![]()
<script type="text/javascript"> Sub AutoColor() Dim LDay As String Dim LColumn As Integer Dim LFound As Boolean On Error GoTo Err_Execute 'Retrieve date value to search for LDay = Sheets("Rolling Plan").Range("B4").Value Sheets("Plan").Select 'Start at column B LColumn = 2 LFound = False While LFound = False 'Encountered blank cell in row 2, terminate search If Len(Cells(2, LColumn)) = 0 Then MsgBox "No matching date was found." Exit Sub 'Found match in row 2 ElseIf Cells(2, LColumn) = LDay Then Sheets("Plan").Select Cells(3, LColumn).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 40000 .TintAndShade = 0 .PatternTintAndShade = 0 End With LFound = True MsgBox "The data has been successfully copied." 'Continue searching Else LColumn = LColumn + 1 End If Wend On Error GoTo 0 Exit Sub Err_Execute: MsgBox "An error occurred." End Sub </script>











LinkBack URL
About LinkBacks
Register To Reply
Bookmarks