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 - I feel like an idiot!
I was ref the wrong cell Projects was not E5 & E6 but E5 and F5. Hours wasted for lack of paying attention to details. (I shall flog myself 7 times) Now maybe I can try your original suggestion and it work better. I do apologize.

 

[ErinJ]

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

Ok so I made that small change and it still doesn't work. I also just realized there are only 256 columns in excel and I need 366 for this to work. (365 days plus 1 for job titles) Any thoughts?

 

[VoG]

Date comparison can be a bit iffy in VBA. Maybe

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

Re the 256 column limitation, you would need to reconfigure your project to use rows instead of columns for the dates or upgrade to Excel 2007 or later.

 

[ErinJ]

Using Excel 2007 and after I got all the lines added I got a compile error procedure to large.

Any thoughts?

 

[VoG]

If you save the file in .xlsm format, close it then reopen it you should find that you have a lot more than 256 columns.

As far as I know the maximum size of a compiled procedure is 44K which is pretty massive.