Conditional Formatting Question

S

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
Office Version
  1. 365
Platform
  1. Windows
I have the code below that conditionally formats the text boxes in a UserForm based on the data coming from the worksheet. Trying to figure out how to make use of this code to reset the formatting on the worksheet with the same formatting used in this code for the UserForm. I would think by looking at this most of the work is done, I just don't know how to reverse in order to set all of the formatting to match on the worksheet. Thanks, SS

VBA Code: 
Private Sub UserForm_Activate()

Me.txtJobName.Locked = True ' set this property in Textbox Properties or with VBA like this
Me.txtJobName.Text = Sheets("Quick Search Job Status").Range("B3").Value

Dim wb As Workbook
Dim ws As Worksheet   'Added SPS,06/16/22
Dim tb As ListObject
Dim frm As Object    'UserForm
Dim job_name As String
Dim i As Long

Set wb = ThisWorkbook

Set ws = wb.Sheets("Jobs") 'Added SPS,06/16/22, worksheet the table is on

Set tb = ws.ListObjects("G2JobList")

Set frm = Job_Status
   
    With frm
   
   
      Application.WindowState = xlMaximized
      Job_Status.Height = Application.Height
      'Job_Status.Width = Application.Width

   
   
      job_name = Trim(.txtJobName.Text)
      
      For i = 1 To tb.DataBodyRange.Rows.Count
      
         If tb.ListColumns("Job Name").DataBodyRange.Cells(i).Value = job_name Then

Populate_Job_Status_Form

'COLOR LEGEND:

'Cell Color - Light Red = .BackColor = RGB(255, 199, 206)
'Font Color - Burgundy = .ForeColor = RGB(156, 0, 6)

'Cell Color - Light Yellow = .BackColor = RGB(255, 235, 156)
'Font Color - Dark Brown = .ForeColor = RGB(156, 87, 0)
            
'Cell Color - Light Green = .BackColor = RGB(198, 239, 206)
'Font Color - Dark Green = .ForeColor = RGB(0, 97, 0)
           
'Cell Color - Light Blue = .BackColor = RGB(184, 204, 228)
'Font Color - Dark Blue = .ForeColor = RGB(0, 0, 255)

            'ENT
            If tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtJobName.BackColor = RGB(255, 199, 206)
                Me.txtJobName.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then

                Me.txtJobName.BackColor = RGB(255, 235, 156)
                Me.txtJobName.ForeColor = RGB(156, 87, 0)

            End If


            'ENT
            If tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtENTREQDDate.BackColor = RGB(255, 199, 206)
                Me.txtENTREQDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then
                Me.txtENTREQDDate.BackColor = RGB(255, 235, 156)
                Me.txtENTREQDDate.ForeColor = RGB(156, 87, 0)

            End If

            If tb.ListColumns("ENT" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <= tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("ENT" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtENTRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtENTRCVDDate.ForeColor = RGB(0, 97, 0)

            End If

            If tb.ListColumns("ENT" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value > tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("ENT" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtENTRCVDDate.BackColor = RGB(255, 199, 206)
                Me.txtENTRCVDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("ENT" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = "Complete" _
            And tb.ListColumns("ENT" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtENTRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtENTRCVDDate.ForeColor = RGB(0, 97, 0)

            End If



            'FXTR
            If tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtJobName.BackColor = RGB(255, 199, 206)
                Me.txtJobName.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then

                Me.txtJobName.BackColor = RGB(255, 235, 156)
                Me.txtJobName.ForeColor = RGB(156, 87, 0)

            End If


            'FXTR
            If tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtFXTRREQDDate.BackColor = RGB(255, 199, 206)
                Me.txtFXTRREQDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then
                Me.txtFXTRREQDDate.BackColor = RGB(255, 235, 156)
                Me.txtFXTRREQDDate.ForeColor = RGB(156, 87, 0)

            End If

            If tb.ListColumns("FXTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <= tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("FXTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtFXTRRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtFXTRRCVDDate.ForeColor = RGB(0, 97, 0)

            End If

            If tb.ListColumns("FXTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value > tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("FXTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtFXTRRCVDDate.BackColor = RGB(255, 199, 206)
                Me.txtFXTRRCVDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("FXTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = "Complete" _
            And tb.ListColumns("FXTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtFXTRRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtFXTRRCVDDate.ForeColor = RGB(0, 97, 0)

            End If



            'CONTR
            If tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtJobName.BackColor = RGB(255, 199, 206)
                Me.txtJobName.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then

                Me.txtJobName.BackColor = RGB(255, 235, 156)
                Me.txtJobName.ForeColor = RGB(156, 87, 0)

            End If


            'CONTR
            If tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtCONTRREQDDate.BackColor = RGB(255, 199, 206)
                Me.txtCONTRREQDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then
                Me.txtCONTRREQDDate.BackColor = RGB(255, 235, 156)
                Me.txtCONTRREQDDate.ForeColor = RGB(156, 87, 0)

            End If

            If tb.ListColumns("CONTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <= tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("CONTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtCONTRRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtCONTRRCVDDate.ForeColor = RGB(0, 97, 0)

            End If

            If tb.ListColumns("CONTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value > tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("CONTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtCONTRRCVDDate.BackColor = RGB(255, 199, 206)
                Me.txtCONTRRCVDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("CONTR" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = "Complete" _
            And tb.ListColumns("CONTR" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtCONTRRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtCONTRRCVDDate.ForeColor = RGB(0, 97, 0)

            End If



            
            
            'Wiring
            If tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtJobName.BackColor = RGB(255, 199, 206)
                Me.txtJobName.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then

                Me.txtJobName.BackColor = RGB(255, 235, 156)
                Me.txtJobName.ForeColor = RGB(156, 87, 0)

            End If


            'Wiring
            If tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date _
            And tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtWiringREQDDate.BackColor = RGB(255, 199, 206)
                Me.txtWiringREQDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value >= Date _
            And tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value < Date + 30 Then
                Me.txtWiringREQDDate.BackColor = RGB(255, 235, 156)
                Me.txtWiringREQDDate.ForeColor = RGB(156, 87, 0)

            End If

            If tb.ListColumns("Wiring" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <= tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("Wiring" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtWiringRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtWiringRCVDDate.ForeColor = RGB(0, 97, 0)

            End If

            If tb.ListColumns("Wiring" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value > tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value _
            And tb.ListColumns("Wiring" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtWiringRCVDDate.BackColor = RGB(255, 199, 206)
                Me.txtWiringRCVDDate.ForeColor = RGB(156, 0, 6)

            End If

            If tb.ListColumns("Wiring" & Chr(10) & "REQD Date").DataBodyRange.Cells(i).Value = "Complete" _
            And tb.ListColumns("Wiring" & Chr(10) & "RCVD Date").DataBodyRange.Cells(i).Value <> "" Then
                Me.txtWiringRCVDDate.BackColor = RGB(198, 239, 206)
                Me.txtWiringRCVDDate.ForeColor = RGB(0, 97, 0)

            End If



            'Job Status Last Update
            
            If tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value <= Date _
            And tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value > Date - 7 Then
                Me.txtLastUpdateDate.BackColor = RGB(198, 239, 206)
                Me.txtLastUpdateDate.ForeColor = RGB(0, 97, 0)

            End If
            
            If tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value <= Date - 7 _
            And tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value > Date - 14 Then
                Me.txtLastUpdateDate.BackColor = RGB(255, 235, 156)
                Me.txtLastUpdateDate.ForeColor = RGB(156, 87, 0)

            End If
            
            If tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value <= Date - 14 _
            And tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value > Date - 30 Then
                Me.txtLastUpdateDate.BackColor = RGB(184, 204, 228)
                Me.txtLastUpdateDate.ForeColor = RGB(0, 0, 255)

            End If
            
            If tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value <= Date - 30 Then
                Me.txtLastUpdateDate.BackColor = RGB(255, 199, 206)
                Me.txtLastUpdateDate.ForeColor = RGB(156, 0, 6)

            End If
        
        
            If tb.ListColumns("Job Status" & Chr(10) & "Last Update").DataBodyRange.Cells(i).Value = "" Then
                Me.txtLastUpdateDate.BackColor = RGB(255, 199, 206)
                Me.txtLastUpdateDate.ForeColor = RGB(156, 0, 6)

            End If
        
        
        Exit For
    
    End If
    
    Next
   
    End With

NumLockCorrector


End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
I've made some progress on this on my own but am hanging up at one other conditional format I need added. I need to find a way to make the number 2 and 3 rules below highlight those same colors in column 'B' when they are true. For example if a date in one of the columns under rule 3 is less than Today() and that date turns light red with dark red font, I need the same thing to happen to the Job Name under column 'B'. The column header for column 'B' is "Job Name". Any assistance would be greatly appreciated. Thanks, SS



VBA Code: 
Sub ConditionalFormatReset()
'
' ConditionalFormatReset Macro

Dim wb As Workbook
Dim ws As Worksheet   'Added SPS,06/16/22
Dim tb As ListObject

Set wb = ThisWorkbook

Set ws = wb.Sheets("Jobs") 'Added SPS,06/16/22, worksheet the table is on

Set tb = ws.ListObjects("G2JobList")
      
'COLOR LEGEND:

'Cell Color - Light Red = .BackColor = RGB(255, 199, 206)
'Font Color - Burgundy = .ForeColor = RGB(156, 0, 6)

'Cell Color - Light Yellow = .BackColor = RGB(255, 235, 156)
'Font Color - Dark Brown = .ForeColor = RGB(156, 87, 0)
            
'Cell Color - Light Green = .BackColor = RGB(198, 239, 206)
'Font Color - Dark Green = .ForeColor = RGB(0, 97, 0)
           
'Cell Color - Light Blue = .BackColor = RGB(184, 204, 228)
'Font Color - Dark Blue = .ForeColor = RGB(0, 0, 255)
     
    

'No 8 Rule Looking for Job Status Last Update Dates Over 30 Days Ago
    Range("G2JobList[[Job Status" & Chr(10) & "Last Update]]").Select
    
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=TODAY()-30"
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

                    With Selection.FormatConditions(1).Font
                        .Color = RGB(156, 0, 6)    'Dark Red
                        .TintAndShade = 0
                    End With
                    With Selection.FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = RGB(255, 199, 206)    'Light Red
                        .TintAndShade = 0
                    End With
    
       
'No 7 Rule Looking for Job Status Last Update Dates 16 to 30 Days Ago
    Range("G2JobList[[Job Status" & Chr(10) & "Last Update]]").Select

        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=TODAY()-16", Formula2:="=TODAY()-30"
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                    With Selection.FormatConditions(1).Font
                        .Color = RGB(0, 0, 255)    'Dark Blue
                        .TintAndShade = 0
                    End With
                    With Selection.FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = RGB(184, 204, 228)    'Light Blue
                        .TintAndShade = 0
                    End With
       
       
'No 6 Rule Looking for Job Status Last Update Dates 8 to 15 Days Ago
    Range("G2JobList[[Job Status" & Chr(10) & "Last Update]]").Select

        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=TODAY()-8", Formula2:="=TODAY()-15"
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                    With Selection.FormatConditions(1).Font
                        .Color = RGB(156, 87, 0)
                        .TintAndShade = 0
                    End With
                    With Selection.FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = RGB(255, 235, 156)
                        .TintAndShade = 0
                    End With
       
       
'No 5 Rule Looking for Job Status Last Update Dates Today to 7 Days Ago
    Range("G2JobList[[Job Status" & Chr(10) & "Last Update]]").Select

        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=TODAY()", Formula2:="=TODAY()-7"
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                
                    With Selection.FormatConditions(1).Font
                        .Color = RGB(0, 97, 0)
                        .TintAndShade = 0
                    End With
                    With Selection.FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = RGB(198, 239, 206)
                        .TintAndShade = 0
                    End With


''No 4 Rule Looking for Job Status Last Update Dates Over 30 Days Ago
'    Range("G2JobList[[Job Status" & Chr(10) & "Last Update]]").Select
'
'                Selection.FormatConditions.Add Type:=xlBlanksCondition
'                        '..........................No color for Font or Interior for Blank Cells
'                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    


'No 3 Rule Looking for PO REQD Dates That Are Past Due
    Range("G2JobList[[Cab" & Chr(10) & "REQD Date]],G2JobList[[ENT" & Chr(10) & "REQD Date]],G2JobList[[FXTR" & Chr(10) & _
        "REQD Date]],G2JobList[[CONTR" & Chr(10) & "REQD Date]],G2JobList[[Door EQPT" & Chr(10) & _
        "REQD Date]],G2JobList[[Wiring" & Chr(10) & "REQD Date]],G2JobList[[Jack" & Chr(10) & "REQD Date]]").Select

        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=TODAY()"
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

                    With Selection.FormatConditions(1).Font
                        .Color = RGB(156, 0, 6)    'Dark Red
                        .TintAndShade = 0
                    End With
                    With Selection.FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = RGB(255, 199, 206)    'Light Red
                        .TintAndShade = 0
                    End With
    
'No 2 Rule Looking for PO REQD Dates Coming Up in Next 30 Days
    Range("G2JobList[[Cab" & Chr(10) & "REQD Date]],G2JobList[[ENT" & Chr(10) & "REQD Date]],G2JobList[[FXTR" & Chr(10) & _
        "REQD Date]],G2JobList[[CONTR" & Chr(10) & "REQD Date]],G2JobList[[Door EQPT" & Chr(10) & _
        "REQD Date]],G2JobList[[Wiring" & Chr(10) & "REQD Date]],G2JobList[[Jack" & Chr(10) & "REQD Date]]").Select

        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=TODAY()+30"
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

                    With Selection.FormatConditions(1).Font
                        .Color = RGB(156, 87, 0)    'Dark Brown
                        .TintAndShade = 0
                    End With
                    With Selection.FormatConditions(1).Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = RGB(255, 235, 156)    'Light Yellow
                        .TintAndShade = 0
                    End With

    
'No 1 Rule Looking for Blank Cells in REQD Dates for PO's
    Range("G2JobList[[Cab" & Chr(10) & "REQD Date]],G2JobList[[ENT" & Chr(10) & "REQD Date]],G2JobList[[FXTR" & Chr(10) & _
        "REQD Date]],G2JobList[[CONTR" & Chr(10) & "REQD Date]],G2JobList[[Door EQPT" & Chr(10) & _
        "REQD Date]],G2JobList[[Wiring" & Chr(10) & "REQD Date]],G2JobList[[Jack" & Chr(10) & "REQD Date]]").Select

                Selection.FormatConditions.Add Type:=xlBlanksCondition
                        '...............................No color for Font or Interior for Blank Cells
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority



        
    Application.Goto Reference:=Range("A1"), Scroll:=True
        
    Range("A3").Select


End Sub
 
Upvote 0
As I had promised. The entire code is below for my Conditional Formatting of a table using the table headers. I'm sure this will help someone down the road.

VBA Code: 
Sub CFResetAll()

Dim ws As Worksheet
Dim tb As ListObject

    Application.CutCopyMode = False

Set ws = Sheets("Jobs")
Set tb = ws.ListObjects("G2JobList")

'Delete any existing Conditional Formatting
tb.DataBodyRange.FormatConditions.Delete

'Run Subs

    CFResetJobNameCOL
    
    CFResetPaymentStatusCOL
    
    CFResetGroup1StatusCOL
    
    CFResetPOREQDRCVDCOL
    
    CFResetJobStatusLastUpdateCOL


End Sub

Sub CFResetJobNameCOL()

Dim ws As Worksheet
Dim tb As ListObject
Dim My_Range As Range
Dim My_Range1 As Range, My_Range2 As Range, My_Range3 As Range, My_Range4 As Range, My_Range5 As Range, My_Range6 As Range, My_Range7 As Range, All_Range As Range


    Application.CutCopyMode = False
    
Set ws = Sheets("Jobs")
Set tb = ws.ListObjects("G2JobList")
Set My_Range = Range("G2JobList[[Job Name]]")
Set My_Range1 = Range("G2JobList[[Cab" & Chr(10) & "REQD Date]], G2JobList[[Cab" & Chr(10) & "RCVD Date]]")
Set My_Range2 = Range("G2JobList[[ENT" & Chr(10) & "REQD Date]], G2JobList[[ENT" & Chr(10) & "RCVD Date]]")
Set My_Range3 = Range("G2JobList[[FXTR" & Chr(10) & "REQD Date]], G2JobList[[FXTR" & Chr(10) & "RCVD Date]]")
Set My_Range4 = Range("G2JobList[[CONTR" & Chr(10) & "REQD Date]], G2JobList[[CONTR" & Chr(10) & "RCVD Date]]")
Set My_Range5 = Range("G2JobList[[Door EQPT" & Chr(10) & "REQD Date]], G2JobList[[Door EQPT" & Chr(10) & "RCVD Date]]")
Set My_Range6 = Range("G2JobList[[Wiring" & Chr(10) & "REQD Date]], G2JobList[[Wiring" & Chr(10) & "RCVD Date]]")
Set My_Range7 = Range("G2JobList[[Jack" & Chr(10) & "REQD Date]], G2JobList[[Jack" & Chr(10) & "RCVD Date]]")
Set All_Range = Union(My_Range1, My_Range2, My_Range3, My_Range4, My_Range5, My_Range6, My_Range7)

'Today's date for condition
Dim CurrDate As Date
CurrDate = Date

'COLOR LEGEND:

'Font Color     - Burgundy          = .FormatConditions(#).Font.Color = RGB(156, 0, 6)           'Burgundy
'Interior Color - Light Red         = .FormatConditions(#).Interior.Color = RGB(255, 199, 206)       'Light Red

'Font Color     - Dark Brown        = .FormatConditions(#).Font.Color = RGB(156, 87, 0)          'Dark Brown
'Interior Color - Light Yellow      = .FormatConditions(#).Interior.Color = RGB(255, 235, 156)       'Light Yellow
            
'Font Color     - Dark Green        = .FormatConditions(#).Font.Color = RGB(0, 97, 0)            'Dark Green
'Interior Color - Light Green       = .FormatConditions(#).Interior.Color = RGB(198, 239, 206)       'Light Green
           
'Font Color     - Dark Blue         = .FormatConditions(#).Font.Color = RGB(0, 0, 255)           'Dark Blue
'Interior Color - Light Blue        = .FormatConditions(#).Interior.Color = RGB(184, 204, 228)       'Light Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Dark Blue         = .FormatConditions(#).Interior.Color = RGB(36, 64, 98)          'Dark Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)          'Olive Green
                    
'Font Color     - Yellow            = .FormatConditions(#).Font.Color = RGB(255, 255, 0)         'Yellow
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)         'Olive Green
                    
'Font Color     - Black             = .FormatConditions(#).Font.Color = RGB(0, 0, 0)             'Black
'Interior Color - Night Sky Blue    = .FormatConditions(#).Interior.Color = RGB(79, 129, 189)        'Night Sky Blue

'Delete any existing Conditional Formatting
My_Range.FormatConditions.Delete

'Add Format Conditions
With My_Range

'    'No 1 Rule Looking for Jobs with any Cab PO REQD Dates That Are Past Due
    .FormatConditions.Add Type:=xlExpression, Formula1:="" _
    & "=IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""3 CJN""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""5 CJS""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""6 DOJ""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""7 CXL""," _
    & "OR(OR(OR(OR(OR(OR(and(isnumber(INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]"")),datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & ">INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]""))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[ENT" & Chr(10) & "REQD Date]]"")),datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & ">INDIRECT(""G2JobList[@[ENT" & Chr(10) & "REQD Date]]""))))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & "REQD Date]]"")),datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & ">INDIRECT(""G2JobList[@[FXTR" & Chr(10) & "REQD Date]]"")))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & "REQD Date]]"")),datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & ">INDIRECT(""G2JobList[@[CONTR" & Chr(10) & "REQD Date]]"")))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & "REQD Date]]"")),datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & ">INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & "REQD Date]]"")))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & "REQD Date]]"")),datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & ">INDIRECT(""G2JobList[@[Wiring" & Chr(10) & "REQD Date]]"")))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[Jack" & Chr(10) & "REQD Date]]"")),datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & ">INDIRECT(""G2JobList[@[Jack" & Chr(10) & "REQD Date]]"")))" _
    & ",)))"
'    .FormatConditions(1).SetFirstPriority
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(1).Interior.Color = RGB(255, 199, 206)       'Light Red

'    'No 2 Rule Looking for Jobs with any Cab PO REQD Dates That Due in next 30 Days
    .FormatConditions.Add Type:=xlExpression, Formula1:="" _
    & "=IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""3 CJN""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""5 CJS""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""6 DOJ""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""7 CXL""," _
    & "OR(OR(OR(OR(OR(OR(and(isnumber(INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]"")),AND(datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & "<=INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]""),datevalue(" & Chr(34) & CurrDate + 30 & Chr(34) & ")>INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]"")))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[ENT" & Chr(10) & "REQD Date]]"")),AND(datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & "<=INDIRECT(""G2JobList[@[ENT" & Chr(10) & "REQD Date]]""),datevalue(" & Chr(34) & CurrDate + 30 & Chr(34) & ")>INDIRECT(""G2JobList[@[ENT" & Chr(10) & "REQD Date]]"")))))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & "REQD Date]]"")),AND(datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & "<=INDIRECT(""G2JobList[@[FXTR" & Chr(10) & "REQD Date]]""),datevalue(" & Chr(34) & CurrDate + 30 & Chr(34) & ")>INDIRECT(""G2JobList[@[FXTR" & Chr(10) & "REQD Date]]""))))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & "REQD Date]]"")),AND(datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & "<=INDIRECT(""G2JobList[@[CONTR" & Chr(10) & "REQD Date]]""),datevalue(" & Chr(34) & CurrDate + 30 & Chr(34) & ")>INDIRECT(""G2JobList[@[CONTR" & Chr(10) & "REQD Date]]""))))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & "REQD Date]]"")),AND(datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & "<=INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & "REQD Date]]""),datevalue(" & Chr(34) & CurrDate + 30 & Chr(34) & ")>INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & "REQD Date]]""))))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & "REQD Date]]"")),AND(datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & "<=INDIRECT(""G2JobList[@[Wiring" & Chr(10) & "REQD Date]]""),datevalue(" & Chr(34) & CurrDate + 30 & Chr(34) & ")>INDIRECT(""G2JobList[@[Wiring" & Chr(10) & "REQD Date]]""))))," _
    & "and(isnumber(INDIRECT(""G2JobList[@[Jack" & Chr(10) & "REQD Date]]"")),AND(datevalue(" & Chr(34) & CurrDate & Chr(34) & ")" _
    & "<=INDIRECT(""G2JobList[@[Jack" & Chr(10) & "REQD Date]]""),datevalue(" & Chr(34) & CurrDate + 30 & Chr(34) & ")>INDIRECT(""G2JobList[@[Jack" & Chr(10) & "REQD Date]]""))))" _
    & ",)))"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)         'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)    'Light Yellow

End With
    
    Application.CutCopyMode = False
   
    Application.GoTo Reference:=Range("A1"), Scroll:=True
        
    Range("A3").Select


End Sub

Sub CFResetPaymentStatusCOL()
    
Dim ws As Worksheet
Dim tb As ListObject
Dim My_Range As Range

    Application.CutCopyMode = False
    
Set My_Range = Range("G2JobList[[Down" & Chr(10) & "Payment]], G2JobList[[Payment" & Chr(10) & "With" & Chr(10) & "Approval]], G2JobList[[Payment" & _
Chr(10) & "Before" & Chr(10) & "Shipping]]")

'COLOR LEGEND:

'Font Color     - Burgundy          = .FormatConditions(#).Font.Color = RGB(156, 0, 6)           'Burgundy
'Interior Color - Light Red         = .FormatConditions(#).Interior.Color = RGB(255, 199, 206)       'Light Red

'Font Color     - Dark Brown        = .FormatConditions(#).Font.Color = RGB(156, 87, 0)          'Dark Brown
'Interior Color - Light Yellow      = .FormatConditions(#).Interior.Color = RGB(255, 235, 156)       'Light Yellow
            
'Font Color     - Dark Green        = .FormatConditions(#).Font.Color = RGB(0, 97, 0)            'Dark Green
'Interior Color - Light Green       = .FormatConditions(#).Interior.Color = RGB(198, 239, 206)       'Light Green
           
'Font Color     - Dark Blue         = .FormatConditions(#).Font.Color = RGB(0, 0, 255)           'Dark Blue
'Interior Color - Light Blue        = .FormatConditions(#).Interior.Color = RGB(184, 204, 228)       'Light Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Dark Blue         = .FormatConditions(#).Interior.Color = RGB(36, 64, 98)          'Dark Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)          'Olive Green
                    
'Font Color     - Yellow            = .FormatConditions(#).Font.Color = RGB(255, 255, 0)         'Yellow
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)         'Olive Green
                    
'Font Color     - Black             = .FormatConditions(#).Font.Color = RGB(0, 0, 0)             'Black
'Interior Color - Night Sky Blue    = .FormatConditions(#).Interior.Color = RGB(79, 129, 189)        'Night Sky Blue

'Delete any existing Conditional Formatting
My_Range.FormatConditions.Delete
     
'Add Format Conditions
With My_Range
    
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Paid""" 'Paid
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green
    
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""NA""" 'NA
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(2).Interior.Color = RGB(198, 239, 206)     'Light Green
    
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Invoiced""" 'Invoiced
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(3).Interior.Color = RGB(255, 235, 156)       'Light Yellow
    
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""REQD""" 'REQD
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(156, 0, 6)           'Burgundy
    .FormatConditions(4).Interior.Color = RGB(255, 199, 206)       'Light Red
    
End With
    
    Application.CutCopyMode = False
   
    Application.GoTo Reference:=Range("A1"), Scroll:=True
        
    Range("A3").Select


End Sub

Sub CFResetGroup1StatusCOL()
    
Dim ws As Worksheet
Dim tb As ListObject
Dim My_Range As Range

    Application.CutCopyMode = False
    
Set My_Range = Range("G2JobList[[Group 1 Status]]")

'COLOR LEGEND:

'Font Color     - Burgundy          = .FormatConditions(#).Font.Color = RGB(156, 0, 6)           'Burgundy
'Interior Color - Light Red         = .FormatConditions(#).Interior.Color = RGB(255, 199, 206)       'Light Red

'Font Color     - Dark Brown        = .FormatConditions(#).Font.Color = RGB(156, 87, 0)          'Dark Brown
'Interior Color - Light Yellow      = .FormatConditions(#).Interior.Color = RGB(255, 235, 156)       'Light Yellow
            
'Font Color     - Dark Green        = .FormatConditions(#).Font.Color = RGB(0, 97, 0)            'Dark Green
'Interior Color - Light Green       = .FormatConditions(#).Interior.Color = RGB(198, 239, 206)       'Light Green
           
'Font Color     - Dark Blue         = .FormatConditions(#).Font.Color = RGB(0, 0, 255)           'Dark Blue
'Interior Color - Light Blue        = .FormatConditions(#).Interior.Color = RGB(184, 204, 228)       'Light Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Dark Blue         = .FormatConditions(#).Interior.Color = RGB(36, 64, 98)          'Dark Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)          'Olive Green
                    
'Font Color     - Yellow            = .FormatConditions(#).Font.Color = RGB(255, 255, 0)         'Yellow
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)         'Olive Green
                    
'Font Color     - Black             = .FormatConditions(#).Font.Color = RGB(0, 0, 0)             'Black
'Interior Color - Night Sky Blue    = .FormatConditions(#).Interior.Color = RGB(79, 129, 189)        'Night Sky Blue

'Delete any existing Conditional Formatting
My_Range.FormatConditions.Delete
     
'Add Format Conditions
With My_Range

    'No 1 Rule Looking for Group 1 Status with text string "Out for Approval" to set the color to

    .FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
    String:="Out for Approval" 'Out for Approval
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(255, 255, 255)       'White
    .FormatConditions(1).Interior.Color = RGB(36, 64, 98)          'Dark Blue

    'No 2 Rule Looking for Group 1 Status with text string "OFA" to set the color to
    .FormatConditions.Add Type:=xlTextString, TextOperator:=xlContains, _
    String:="OFA" 'OFA
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(255, 255, 255)       'White
    .FormatConditions(2).Interior.Color = RGB(36, 64, 98)          'Dark Blue
    
    'No 3 Rule Looking for Group 1 Status with "Complete & Shipped" to set the color to
    
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Complete & Shipped""" 'Complete & Shipped
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(255, 255, 255)       'White
    .FormatConditions(3).Interior.Color = RGB(128, 128, 0)         'Olive Green
    
    'No 4 Rule Looking for Group 1 Status with "Complete & Not Shipped" to set the color to
    
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Complete & Not Shipped""" 'Complete & Not Shipped
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(255, 255, 0)         'Yellow
    .FormatConditions(4).Interior.Color = RGB(128, 128, 0)         'Olive Green

    'No 5 Rule Looking for Group 1 Status with "See MRL/OHT Job List" to set the color to

    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""See MRL/OHT Job List""" 'See MRL/OHT Job List
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(0, 0, 0)             'Black
    .FormatConditions(5).Interior.Color = RGB(79, 129, 189)        'Night Sky Blue

End With
    
    Application.CutCopyMode = False
   
    Application.GoTo Reference:=Range("A1"), Scroll:=True
        
    Range("A3").Select


End Sub

Sub CFResetPOREQDRCVDCOL()

Dim ws As Worksheet
Dim tb As ListObject
Dim My_Range1 As Range, My_Range2 As Range, My_Range3 As Range, My_Range4 As Range, My_Range5 As Range, My_Range6 As Range, My_Range7 As Range, All_Range As Range

'Today's date for condition
Dim CurrDate As Date
CurrDate = Date

    Application.CutCopyMode = False
    
Set My_Range1 = Range("G2JobList[[Cab" & Chr(10) & "REQD Date]], G2JobList[[Cab" & Chr(10) & "RCVD Date]]")
Set My_Range2 = Range("G2JobList[[ENT" & Chr(10) & "REQD Date]], G2JobList[[ENT" & Chr(10) & "RCVD Date]]")
Set My_Range3 = Range("G2JobList[[FXTR" & Chr(10) & "REQD Date]], G2JobList[[FXTR" & Chr(10) & "RCVD Date]]")
Set My_Range4 = Range("G2JobList[[CONTR" & Chr(10) & "REQD Date]], G2JobList[[CONTR" & Chr(10) & "RCVD Date]]")
Set My_Range5 = Range("G2JobList[[Door EQPT" & Chr(10) & "REQD Date]], G2JobList[[Door EQPT" & Chr(10) & "RCVD Date]]")
Set My_Range6 = Range("G2JobList[[Wiring" & Chr(10) & "REQD Date]], G2JobList[[Wiring" & Chr(10) & "RCVD Date]]")
Set My_Range7 = Range("G2JobList[[Jack" & Chr(10) & "REQD Date]], G2JobList[[Jack" & Chr(10) & "RCVD Date]]")
Set All_Range = Union(My_Range1, My_Range2, My_Range3, My_Range4, My_Range5, My_Range6, My_Range7)

'COLOR LEGEND:

'Font Color     - Burgundy          = .FormatConditions(#).Font.Color = RGB(156, 0, 6)           'Burgundy
'Interior Color - Light Red         = .FormatConditions(#).Interior.Color = RGB(255, 199, 206)       'Light Red

'Font Color     - Dark Brown        = .FormatConditions(#).Font.Color = RGB(156, 87, 0)          'Dark Brown
'Interior Color - Light Yellow      = .FormatConditions(#).Interior.Color = RGB(255, 235, 156)       'Light Yellow
            
'Font Color     - Dark Green        = .FormatConditions(#).Font.Color = RGB(0, 97, 0)            'Dark Green
'Interior Color - Light Green       = .FormatConditions(#).Interior.Color = RGB(198, 239, 206)       'Light Green
           
'Font Color     - Dark Blue         = .FormatConditions(#).Font.Color = RGB(0, 0, 255)           'Dark Blue
'Interior Color - Light Blue        = .FormatConditions(#).Interior.Color = RGB(184, 204, 228)       'Light Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Dark Blue         = .FormatConditions(#).Interior.Color = RGB(36, 64, 98)          'Dark Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)          'Olive Green
                    
'Font Color     - Yellow            = .FormatConditions(#).Font.Color = RGB(255, 255, 0)         'Yellow
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)         'Olive Green
                    
'Font Color     - Black             = .FormatConditions(#).Font.Color = RGB(0, 0, 0)             'Black
'Interior Color - Night Sky Blue    = .FormatConditions(#).Interior.Color = RGB(79, 129, 189)        'Night Sky Blue

'Delete any existing Conditional Formatting  "I MIGHT END UP HAVING TO ONLY ALLOW IT TO DELETE THE FORMAT CONDITIONS FROM A DEFINED ARRAY RANGE"
All_Range.FormatConditions.Delete

'Add Format Conditions
With My_Range1

    'No 1 Rule Looking for Jobs with any Cab PO REQD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[Cab" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 2 Rule Looking for Jobs with any Cab PO REQD Dates that are due in the next 30 Days
    Range("G2JobList[[Cab" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)       'Light Yellow

    'No 3 Rule Looking for Jobs with any Cab PO REQD Dates that are Late
    Range("G2JobList[[Cab" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(3).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 4 Rule Looking for Jobs with any Cab PO RCVD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[Cab" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 5 Rule Looking for Jobs with any Cab PO REQD Dates that are Late
    Range("G2JobList[[Cab" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Cab" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Cab" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(5).Interior.Color = RGB(255, 199, 206)       'Light Red

End With


With My_Range2

    'No 6 Rule Looking for Jobs with any ENT PO REQD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[ENT" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 7 Rule Looking for Jobs with any ENT PO REQD Dates that are due in the next 30 Days
    Range("G2JobList[[ENT" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)       'Light Yellow

    'No 8 Rule Looking for Jobs with any ENT PO REQD Dates that are Late
    Range("G2JobList[[ENT" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[ENT" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(3).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 9 Rule Looking for Jobs with any ENT PO RCVD Dates where the Componet was RCVD On-Time

    Range("G2JobList[[ENT" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 10 Rule Looking for Jobs with any ENT PO REQD Dates that are Late
    Range("G2JobList[[ENT" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[ENT" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[ENT" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(5).Interior.Color = RGB(255, 199, 206)       'Light Red

End With


With My_Range3

    'No 11 Rule Looking for Jobs with any FXTR PO REQD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[FXTR" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green
    
    'No 12 Rule Looking for Jobs with any FXTR PO REQD Dates that are due in the next 30 Days
    Range("G2JobList[[FXTR" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)       'Light Yellow

    'No 13 Rule Looking for Jobs with any FXTR PO REQD Dates that are Late
    Range("G2JobList[[FXTR" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[FXTR" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(3).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 14 Rule Looking for Jobs with any FXTR PO RCVD Dates where the Componet was RCVD On-Time

    Range("G2JobList[[FXTR" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 15 Rule Looking for Jobs with any FXTR PO REQD Dates that are Late
    Range("G2JobList[[FXTR" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[FXTR" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[FXTR" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(5).Interior.Color = RGB(255, 199, 206)       'Light Red

End With


With My_Range4

    'No 16 Rule Looking for Jobs with any CONTR PO REQD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[CONTR" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 17 Rule Looking for Jobs with any CONTR PO REQD Dates that are due in the next 30 Days
    Range("G2JobList[[CONTR" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)       'Light Yellow
    
    'No 18 Rule Looking for Jobs with any CONTR PO REQD Dates that are Late
    Range("G2JobList[[CONTR" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[CONTR" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(3).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 19 Rule Looking for Jobs with any CONTR PO RCVD Dates where the Componet was RCVD On-Time

    Range("G2JobList[[CONTR" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 20 Rule Looking for Jobs with any CONTR PO REQD Dates that are Late
    Range("G2JobList[[CONTR" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[CONTR" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[CONTR" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(5).Interior.Color = RGB(255, 199, 206)       'Light Red

End With


With My_Range5

    'No 21 Rule Looking for Jobs with any Door EQPT PO REQD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[Door EQPT" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 22 Rule Looking for Jobs with any Door EQPT PO REQD Dates that are due in the next 30 Days
    Range("G2JobList[[Door EQPT" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)       'Light Yellow

    'No 23 Rule Looking for Jobs with any Door EQPT PO REQD Dates that are Late
    Range("G2JobList[[Door EQPT" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(3).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 24 Rule Looking for Jobs with any Door EQPT PO RCVD Dates where the Componet was RCVD On-Time

    Range("G2JobList[[Door EQPT" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 25 Rule Looking for Jobs with any Door EQPT PO REQD Dates that are Late
    Range("G2JobList[[Door EQPT" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Door EQPT" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(5).Interior.Color = RGB(255, 199, 206)       'Light Red

End With


With My_Range6

    'No 26 Rule Looking for Jobs with any Wiring PO REQD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[Wiring" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 27 Rule Looking for Jobs with any Wiring PO REQD Dates that are due in the next 30 Days
    Range("G2JobList[[Wiring" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)       'Light Yellow

    'No 28 Rule Looking for Jobs with any Wiring PO REQD Dates that are Late
    Range("G2JobList[[Wiring" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Wiring" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(3).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 29 Rule Looking for Jobs with any Wiring PO RCVD Dates where the Componet was RCVD On-Time

    Range("G2JobList[[Wiring" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 30 Rule Looking for Jobs with any Wiring PO REQD Dates that are Late
    Range("G2JobList[[Wiring" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Wiring" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Wiring" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(5).Interior.Color = RGB(255, 199, 206)       'Light Red

End With


With My_Range7

    'No 31 Rule Looking for Jobs with any Jack PO REQD Dates where the Componet was RCVD On-Time
    Range("G2JobList[[Jack" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(1).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 32 Rule Looking for Jobs with any Jack PO REQD Dates that are due in the next 30 Days
    Range("G2JobList[[Jack" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
    Formula1:="=TODAY()", Formula2:="=TODAY()+30"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(2).Interior.Color = RGB(255, 235, 156)       'Light Yellow

    'No 33 Rule Looking for Jobs with any Jack PO REQD Dates that are Late
    Range("G2JobList[[Jack" & Chr(10) & "REQD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Jack" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(3).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(3).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 34 Rule Looking for Jobs with any Jack PO RCVD Dates where the Componet was RCVD On-Time

    Range("G2JobList[[Jack" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(NOT(ISTEXT(INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]""))),IF(NOT(ISBLANK(INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]""))), INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]"")>=INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "RCVD Date]]""),))"
    .FormatConditions(4).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)           'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)     'Light Green

    'No 35 Rule Looking for Jobs with any Jack PO REQD Dates that are Late
    Range("G2JobList[[Jack" & Chr(10) & "RCVD Date]]").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(not(ISBLANK(INDIRECT(""G2JobList[@[Jack" & Chr(10) & _
    "REQD Date]]""))), datevalue(" & Chr(34) & CurrDate & Chr(34) & ")>=INDIRECT(""G2JobList[@[Jack" & Chr(10) & "REQD Date]]""),)"
    .FormatConditions(5).StopIfTrue = False
    .FormatConditions(5).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(5).Interior.Color = RGB(255, 199, 206)       'Light Red

End With

    
'****************SAVE FOR LATER (NO FORMAT CONDTION)*******************
''    .FormatConditions(3).Font.ColorIndex = xlAutomatic
''    .FormatConditions(3).Font.TintAndShade = 0
''    .FormatConditions(3).Interior.Pattern = xlNone
''    .FormatConditions(3).Interior.TintAndShade = 0
''    .FormatConditions(3).Interior.PatternTintAndShade = 0
'****************SAVE FOR LATER (NO FORMAT CONDTION)*******************

    
    Application.CutCopyMode = False
   
    Application.GoTo Reference:=Range("A1"), Scroll:=True
        
    Range("A3").Select


End Sub

Sub CFResetJobStatusLastUpdateCOL()

Dim ws As Worksheet
Dim tb As ListObject
Dim My_Range As Range

'Today's date for condition
Dim CurrDate As Date
CurrDate = Date

    Application.CutCopyMode = False
    
Set My_Range = Range("G2JobList[[Job Status" & Chr(10) & "Last Update]]")

'COLOR LEGEND:

'Font Color     - Burgundy          = .FormatConditions(#).Font.Color = RGB(156, 0, 6)           'Burgundy
'Interior Color - Light Red         = .FormatConditions(#).Interior.Color = RGB(255, 199, 206)       'Light Red

'Font Color     - Dark Brown        = .FormatConditions(#).Font.Color = RGB(156, 87, 0)          'Dark Brown
'Interior Color - Light Yellow      = .FormatConditions(#).Interior.Color = RGB(255, 235, 156)       'Light Yellow
            
'Font Color     - Dark Green        = .FormatConditions(#).Font.Color = RGB(0, 97, 0)            'Dark Green
'Interior Color - Light Green       = .FormatConditions(#).Interior.Color = RGB(198, 239, 206)       'Light Green
           
'Font Color     - Dark Blue         = .FormatConditions(#).Font.Color = RGB(0, 0, 255)           'Dark Blue
'Interior Color - Light Blue        = .FormatConditions(#).Interior.Color = RGB(184, 204, 228)       'Light Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Dark Blue         = .FormatConditions(#).Interior.Color = RGB(36, 64, 98)          'Dark Blue
     
'Font Color     - White             = .FormatConditions(#).Font.Color = RGB(255, 255, 255)       'White
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)          'Olive Green
                    
'Font Color     - Yellow            = .FormatConditions(#).Font.Color = RGB(255, 255, 0)         'Yellow
'Interior Color - Olive Green       = .FormatConditions(#).Interior.Color = RGB(128, 128, 0)         'Olive Green
                    
'Font Color     - Black             = .FormatConditions(#).Font.Color = RGB(0, 0, 0)             'Black
'Interior Color - Night Sky Blue    = .FormatConditions(#).Interior.Color = RGB(79, 129, 189)        'Night Sky Blue

'Delete any existing Conditional Formatting  "I MIGHT END UP HAVING TO ONLY ALLOW IT TO DELETE THE FORMAT CONDITIONS FROM A DEFINED ARRAY RANGE"
My_Range.FormatConditions.Delete

With My_Range

    'No 1 Rule Looking for Job Status Last Update Dates Over 30 Days Ago
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""5 CJS""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""7 CXL""," _
    & "datevalue(" & Chr(34) & CurrDate - 31 & Chr(34) & ") >=INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]""),))"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(1).Font.Color = RGB(156, 0, 6)            'Burgundy
    .FormatConditions(1).Interior.Color = RGB(255, 199, 206)       'Light Red

    'No 2 Rule Looking for Job Status Last Update Dates 16 to 30 Days Ago
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""5 CJS""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""7 CXL""," _
    & "AND(datevalue(" & Chr(34) & CurrDate - 16 & Chr(34) & ") >=INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]"")," _
    & "datevalue(" & Chr(34) & CurrDate - 31 & Chr(34) & ") <INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]"")),))"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(2).Font.Color = RGB(0, 0, 255)           'Dark Blue
    .FormatConditions(2).Interior.Color = RGB(184, 204, 228)       'Light Blue

    'No 3 Rule Looking for Job Status Last Update Dates 8 to 15 Days Ago
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""5 CJS""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""7 CXL""," _
    & "AND(datevalue(" & Chr(34) & CurrDate - 8 & Chr(34) & ") >=INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]"")," _
    & "datevalue(" & Chr(34) & CurrDate - 16 & Chr(34) & ") <INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]"")),))"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(3).Font.Color = RGB(156, 87, 0)          'Dark Brown
    .FormatConditions(3).Interior.Color = RGB(255, 235, 156)       'Light Yellow

    'No 4 Rule Looking for Job Status Last Update Dates Today to 7 Days Ago
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""5 CJS""," _
    & "IF(INDIRECT(""G2JobList[@[Job" & Chr(10) & "Status]]"") <> ""7 CXL""," _
    & "AND(datevalue(" & Chr(34) & CurrDate - 0 & Chr(34) & ") >=INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]"")," _
    & "datevalue(" & Chr(34) & CurrDate - 8 & Chr(34) & ") <INDIRECT(""G2JobList[@[Job Status" & Chr(10) & "Last Update]]"")),))"
    .FormatConditions(2).StopIfTrue = False
    .FormatConditions(4).Font.Color = RGB(0, 97, 0)            'Dark Green
    .FormatConditions(4).Interior.Color = RGB(198, 239, 206)       'Light Green

End With
    
'****************SAVE FOR LATER (NO FORMAT CONDTION)*******************
''    .FormatConditions(3).Font.ColorIndex = xlAutomatic
''    .FormatConditions(3).Font.TintAndShade = 0
''    .FormatConditions(3).Interior.Pattern = xlNone
''    .FormatConditions(3).Interior.TintAndShade = 0
''    .FormatConditions(3).Interior.PatternTintAndShade = 0
'****************SAVE FOR LATER (NO FORMAT CONDTION)*******************
    
    Application.CutCopyMode = False
   
    Application.GoTo Reference:=Range("A1"), Scroll:=True
        
    Range("A3").Select


End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
Conditional Formatting of Table Cell Value in UserForm
Replies
1
Views
413
sspatriots
S
S
  • Solved
Trying to reverse code that pulls data from table to make it write to the table
2
Replies
13
Views
771
Anthony47
A
S
  • Solved
Select Multiple Table columns by header name (vba)
Replies
7
Views
7K
Akuini
Akuini
S
  • Solved
Tweak Macro that Imports specific table columns from a different workbook to filter a column first
Replies
4
Views
443
sspatriots
S
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
396
rahildhody
R

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top