Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    5

    Unanswered: No color fill in cell in different file

    I have a Macro with this code it runs fine it the file I saved it in, but when i try to run it on another excel file the code still runs but in the ChangeColor method, it doesn't fill the cells. It runs step by step fine it just doesn't fill the color. Here is my code.

    Code:
    Sub ChangeColor()
    
    '---------------------------ChangeColor-------------------------
    
    Dim rCell As Range
    Worksheets("MSS Open Purchase Orders").Select
    With Sheet1
        For Each rCell In .Range("N4", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                 ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
    End With
    
    '------------------------------I-------------------------------
    
     Worksheets("I").Select
    With Sheet2
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
     '------------------------------O-------------------------------
       Worksheets("O").Select
    With Sheet3
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
       '------------------------------E-------------------------------
       Worksheets("E").Select
    With Sheet4
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                 ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
      '------------------------------C-------------------------------
        Worksheets("C").Select
    With Sheet5
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                 ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
    '------------------------------------Short---------------------------------------
    
    
     ActiveWorkbook.Worksheets("MSS Open Purchase Orders").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MSS Open Purchase Orders").Sort.SortFields.Add Key _
        :=Range("N4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MSS Open Purchase Orders").Sort
        .SetRange Range("N4:N58")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
     End With
    
    End Sub
    Any help is appreciated just started using vba in excel

  2. #2
    Join Date
    Jul 2014
    Posts
    5

    Question No color fill in cell in different file

    I have a Macro with this code it runs fine it the file I saved it in, but when i try to run it on another excel file the code still runs but in the ChangeColor method, it doesn't fill the cells. It runs step by step fine it just doesn't fill the color. Here is my code.

    Code:
    Sub ChangeColor()
    
    '---------------------------ChangeColor-------------------------
    
    Dim rCell As Range
    Worksheets("MSS Open Purchase Orders").Select
    With Sheet1
        For Each rCell In .Range("N4", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                 ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
    End With
    
    '------------------------------I-------------------------------
    
     Worksheets("I").Select
    With Sheet2
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
     '------------------------------O-------------------------------
       Worksheets("O").Select
    With Sheet3
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
       '------------------------------E-------------------------------
       Worksheets("E").Select
    With Sheet4
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                 ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
      '------------------------------C-------------------------------
        Worksheets("C").Select
    With Sheet5
        For Each rCell In .Range("N2", .Cells(.Rows.Count, 14).End(xlUp)).Cells
            If rCell.Value <= Date Then
                rCell.Interior.Color = vbRed
                 ElseIf rCell.Value <= Date + 7 Then
                rCell.Interior.Color = RGB(255, 102, 0)
            ElseIf rCell.Value <= Date + 30 Then
                rCell.Interior.Color = vbYellow
    
            Else
                rCell.Interior.Color = vbGreen
            End If
        Next rCell
     End With
    
    '------------------------------------Short---------------------------------------
    
    
     ActiveWorkbook.Worksheets("MSS Open Purchase Orders").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MSS Open Purchase Orders").Sort.SortFields.Add Key _
        :=Range("N4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MSS Open Purchase Orders").Sort
        .SetRange Range("N4:N58")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
     End With
    
    End Sub

    Any help is appreciated just started using vba in excel

  3. #3
    Join Date
    Sep 2014
    Posts
    37
    Try this....

    Code:
    Sub test()
     
        Dim rCell As Range
        
        Worksheets("MSS Open Purchase Orders").Select
        
        ' set the range of cells to look in
        Set rCell = ActiveSheet.Range(Cells(4, 14), Cells(WorksheetFunction.CountA(Range("N:N")), 14)).Cells
        
        'cycle through those cells and modify interior color as needed.
        With Sheet1
            For Each c In rCell
                If c.Value <= Date Then
                    c.Interior.Color = vbRed
                     ElseIf c.Value <= Date + 7 Then
                    c.Interior.Color = RGB(255, 102, 0)
                ElseIf c.Value <= Date + 30 Then
                    c.Interior.Color = vbYellow
        
                Else
                    c.Interior.Color = vbGreen
                End If
            Next c
        End With
    End Sub

Tags for this Thread

Posting Permissions

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