No color fill in cell in different file

Question

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.

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

'------------------------------------Sort---------------------------------------


 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

I'm guessing you need to refer to the other workbook. For instance, `Worksheets("MSS Open Purchase Orders").Select` will select that worksheet in the workbook where the code resides. Alternatively, you can copy this code to the new workbook and make sure all of the relevant worksheet names are the same. — Matt Cremeens, Sep 10 '14 at 18:05
I have another method that create the specific sheet needed if its not on the worksheet for this colorchange method to avoid that error, the strange thing for me is why does everything else run ok it just doesn't fill the cell with the specified color? @MattCremeens — MoralesJosue, Sep 10 '14 at 18:11
I don't believe it's enough to create and utilize a sheet with that name, you also must include a reference to the **workbook** itself. — Matt Cremeens, Sep 10 '14 at 18:21
BTW why do you have the same code again and again? Why not have a common sub? — Siddharth Rout, Sep 10 '14 at 18:30
I agree with Matt. But I did a test and it seems to work if you change `With Sheet1` to `With Worksheets("Sheet1")` etc. — Zeno, Sep 10 '14 at 18:31
Ok, I copy pasted the code in the new worksheet and it works like it should, is there a way to refer the worksheet from the other one i already have, to avoid copy paste every time i do this report, witch is almost everyday @MattCremeens — MoralesJosue, Sep 10 '14 at 18:33
I added the change with Sheet1 to With Wroksheets("MSS Open Purchase Orders") as @Zeno suggested, how do i proceed in giving the answer and give credit to zeno and matt Cremeens? — MoralesJosue, Sep 10 '14 at 18:48
Like i mentioned im new in vba in general and in excel-vba, this is the fist thing that came to my mind, could you explain how to optimize the code @SiddharthRout — MoralesJosue, Sep 10 '14 at 18:50
I can wait or if you could refer to some other link that could help i don't mind reading and learning @SiddharthRout — MoralesJosue, Sep 10 '14 at 19:02

Matt Cremeens · Answer 1 · 2014-09-10T18:54:48.830

1

There are a number of ways to do this, but if the workbook you want your code to work on is the active workbook, you can reference it as in this example

ActiveWorkbook.Worksheets("MSS Open Purchase Orders").Select

To @Zeno point, you can probably dispense with the

With Sheet1 and End With

etc. and remove the .s.

edited Sep 10 '14 at 18:54

answered Sep 10 '14 at 18:45

Matt Cremeens

4,951
7
38
67

I always give a chance for the poster to rectify the post. If the poster ignores me then only do I downvote ;) – Siddharth Rout Sep 10 '14 at 18:51
Well, it seems to me that he needs to make sure that he is referencing a Workbook that doesn't actually contain the code he wrote. And Sheet1, 2, etc. aren't defined anywhere, and what the OP really seems to be wanting to work in is the sheet I mentioned, but in the ActiveWorkbook. – Matt Cremeens Sep 10 '14 at 18:53
I have posted an answer which will show why I said it was a wrong advice :) Don't worry, I am not downvoting your answer ;) – Siddharth Rout Sep 10 '14 at 19:01

score 1 · Accepted Answer · edited May 23 '17 at 11:49

I see two main issues with your code

You have repeated the code many number of times. You could use a common sub and keep on calling that.
You should avoid the use of .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook You may want to see THIS

Here is how your code can be optimized. This is just an example. Please amend it to suit your needs.

UNTESTED

Sub ChangeColor()
    Dim wb As Workbook, ws As Worksheet

    '~~> Here change it to the relevant workbook
    Set wb = ThisWorkbook

    '~~> Here set the worksheets you want to work with
    Set ws = wb.Worksheets("MSS Open Purchase Orders")

    '~~> Do the coloring
    ColorCells ws

    '~~> Again set the worksheets you want to work with
    Set ws = wb.Worksheets("I")

    '~~> Do the coloring
    ColorCells ws

    '~~> Again set the worksheets you want to work with
    Set ws = wb.Worksheets("O")

    '~~> Do the coloring
    ColorCells ws

    '
    '~~> And So On
    '
End Sub

'~~> Common Sub to color the sheets
Sub ColorCells(sHt As Worksheet)
    Dim rCell As Range

    With sHt
        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
End Sub

The OP is trying to run the code from one workbook to affect another. Doesn't the `ThisWorkbook` refer to the workbook with the code in it? — Matt Cremeens, Sep 10 '14 at 19:02
One can change it to `Set wb = Workbooks("Blah Blah")` or `Set wb = Workbooks.Open("C:\Blah.xlsx")` There is no need to use `ActiveWorkbook` — Siddharth Rout, Sep 10 '14 at 19:04
I got it. Quite right. I guess I was trying to give him a solution that didn't require him to modify his code for each workbook he may want to run the code on. — Matt Cremeens, Sep 10 '14 at 19:04
The problem with `Activeworkbook` is during the execution of the code, you may not realize which workbook is actually "Active" and hence one may get unexpected results :) — Siddharth Rout, Sep 10 '14 at 19:05

No color fill in cell in different file

2 Answers2