conditional formating across worksheets

[ErinJ]

I am creating a gantt chart
I have two sheets:
Projects
Schedules
In Projects I have 2 columns
Start Date
Completion Date
In Schedules I have the same projects mirrored but I have a column for each day of the month/year. I have put the corresponding date in each cell and formated white so no one can see it.
I want the corresponding Job to change the color of the cell to blue if it falls within the cell range of Start Date and Completion Date in Projects.
Here is a sample of code I'm playing with but it doesn't work. I'm probably on the wrong track.

Select Case Range("C6:C371").Value
        Case Is => "PROJECTS!E5" < "PROJECTS!E6"
            Range("C6:C371").FormatConditions(1).Interior.ColorIndex=5
          Case Else
                Range("C6C371").FormatConditions(1).Interior.ColorIndex= White?
    End Select

 

[ErinJ]

 

[VoG]

Try like this

For i = 6 To 371
    With Range("C" & i)
        If .Value >= Sheets("PROJECTS").Range("E5").Value And .Value < Sheets("PROJECTS").Range("E6").Value Then
            .Interior.ColorIndex = 5
          Else
            .Interior.ColorIndex = 2
    End With
Next i

 

[ErinJ]

Private Sub Worksheet_Activate()
For i = 6 To 371
    With Range("C" & i)
        If .Value >= Sheets("PROJECTS").Range("E5").Value And .Value < Sheets("PROJECTS").Range("E6").Value Then
            .Interior.ColorIndex = 5
          Else
            .Interior.ColorIndex = 2
    End With
Next i
End Sub

It didn't do any thing. Actually if gave me a compile errer End With without With

 

[ErinJ]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For i = 6 To 371
    With Range("C" & i)
        If .Value >= Sheets("PROJECTS").Range("E5").Value And .Value < Sheets("PROJECTS").Range("E6").Value Then
            .Interior.ColorIndex = 5
          Else
            .Interior.ColorIndex = 2
    End If
    End With
    Next i
End Sub

I added the End If statement, and the error went away but I can't see that anything is happening.

 

[ErinJ]

Select Case Range("C6").Value
            Case Is >= ['PROJECTS'!E5].Value, Is <= ['PROJECTS'!E6].Value
                Range("C6").Interior.ColorIndex = 5
            Case Else
                Range("C6").Interior.ColorIndex = 2
        End Select

Okay this is what I go so far. The first part works if C6 is greater than or equal to Projects E5 then turn cell blue if not turn cell white. but my second part doesn't work "And if C6 is less than Projects E6 make it blue but if not make it white. Any thoughts?

 

[VoG]

Try an If statement rather than Select Case for this type of comparison.

 

[VoG]

To use Select Case maybe

Select Case Range("C6").Value >= ['PROJECTS'!E5].Value And Range("C6").Value <= ['PROJECTS'!E6].Value
    Case True
        Range("C6").Interior.ColorIndex = 5
    Case Else
        Range("C6").Interior.ColorIndex = 2
End Select

 

[ErinJ]

 If Range("C6").Value <= (['PROJECTS'!E5]) And Range("C6") > (['PROJECTS'!E6]) Then
                Range("C6").Interior.ColorIndex = 3 'Red
                Else
                Range("C6").Interior.ColorIndex = 5 'Blue
        End If

This is what I'm trying but it doesn't seem to work. It seems it is doing a "OR" not a "AND".

If C6 is greater than or equal to E5 and less than E6 then make cell red. If it is less than E5 or more than E6 then Blue. These are all dates C6 has a permanent date and E5 is a start date and E6 is a completion date. What is wrong with my thinking?

Also, even if this does work I would have to repeat this code 365 times for every day of year (Columns) and 28 times for each possible job (rows). Please any help is appreciated.

VOG - I could not get yours to work so I had to try and figure it out that's why I tried Select Case and then ultimately the IF statements. I do appreciate your input.

 

[VoG]

Maybe like this

With Sheets("Schedules")
    If .Range("C6").Value <= Sheets("Projects").Range("E5").Value And .Range("C6") > Sheets("Projects").Range("E6").Value Then
        .Range("C6").Interior.ColorIndex = 3 'Red
    Else
        .Range("C6").Interior.ColorIndex = 5 'Blue
    End If
End If