Hi All,

I need help to figure out how to use VBA to alter a tab's color based on the condition of two cells, as detailed below:

If B1 and J1 are blank, leave tab color GREY.
If B1 has a date, and J1 is blank, turn tab color RED.
If J1 has a date, regardless of B1, turn tab color GREEN.

The name of the sheet I am writing this for is named "Macro", and will ultimately be copied multiple times using the code below. I would like to know if/how I can ensure that the code I am currently trying to figure out will also be in place for the newly copied sheets, refering to their own B1 and J1 cells.

Sub Build_Project_Binder_V2()
    Dim MyCell As Range, MyRange As Range
    
    Set MyRange = Sheets("Unit Detail").Range("A4")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet based on unit numbers list in "Unit Detail" sheet
    Worksheets("Macro").Cells.Copy ActiveSheet.Range("A1") 'copies "Macro" to newly created sheet
    Next MyCell
End Sub

I am using Excel 2010.
Thank you so much in advance for any assistance!

Joe C.