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>
Bookmarks