Using Conditional Formats
 

I have a workbook that I am using to manage my travel with. During the year
I will be in a number of different cities for various periods of time. I am
logging itineraries into one sheet and manually updating another sheet within
the same workbook that displays my schedule in calendar format.

What I would like to be able to do is format the cells in the calendar view
based on my date entries & locations I will be in on those dates. For
example, if I am in Charlotte from 4/1 until 4/15 I'd like the corresponding
calendar cells to have a red pattern color, and if the city were lets say
Savannah it would have green pattern color instead.

Two items here then:

1. Setting conditional format based on city in my itinerary.
2. Cell comparison based on time period (arrive/depart)

Is this possible, or should I continue to update manually?

Thanks so much!

Scott,
I think I can help with the color format of the cell based on city.

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:G10")) Is Nothing _
Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "Savanna": .Interior.ColorIndex = 6
Case "Charlotte": .Interior.ColorIndex = 3
.Font.ColorIndex = 2
Case "Any City": .Interior.ColorIndex = any number 0-56

End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub

If you specify your range of cells in Target, Me.Range you can add a number
of different colorindexes to the cells in that range by the cells contents.
One additional suggestion would be to alter the font color as well as the
cell color to make it easier to read. See the example above for Charlotte.
For Font colors, 1 = Black, and 2 = White.


"Scott" wrote:

I have a workbook that I am using to manage my travel with. During the year
I will be in a number of different cities for various periods of time. I am
logging itineraries into one sheet and manually updating another sheet within
the same workbook that displays my schedule in calendar format.

What I would like to be able to do is format the cells in the calendar view
based on my date entries & locations I will be in on those dates. For
example, if I am in Charlotte from 4/1 until 4/15 I'd like the corresponding
calendar cells to have a red pattern color, and if the city were lets say
Savannah it would have green pattern color instead.

Two items here then:

1. Setting conditional format based on city in my itinerary.
2. Cell comparison based on time period (arrive/depart)

Is this possible, or should I continue to update manually?

Thanks so much!

Formatconditional formatting, formula is

=AND(City_cells="Charlotte",Date_cells=--"04/01/05",Date_cells<=--"04/15/05
")


--

Regards,

Peo Sjoblom


"Scott" wrote in message
...
I have a workbook that I am using to manage my travel with. During the
year
I will be in a number of different cities for various periods of time. I
am
logging itineraries into one sheet and manually updating another sheet
within
the same workbook that displays my schedule in calendar format.

What I would like to be able to do is format the cells in the calendar
view
based on my date entries & locations I will be in on those dates. For
example, if I am in Charlotte from 4/1 until 4/15 I'd like the
corresponding
calendar cells to have a red pattern color, and if the city were lets say
Savannah it would have green pattern color instead.

Two items here then:

1. Setting conditional format based on city in my itinerary.
2. Cell comparison based on time period (arrive/depart)

Is this possible, or should I continue to update manually?

Thanks so much!