Results 1 to 5 of 5

Auto Color whole column when found text "Sunday"

Threaded View

  1. #1
    Registered User
    Join Date
    10-09-2009
    Location
    KL
    MS-Off Ver
    Excel 2003
    Posts
    42

    Auto Color whole column when found text "Sunday"

    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>
    Attached Files Attached Files
    Last edited by Kenji; 10-20-2009 at 10:04 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1