Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel conditional formats | Excel Discussion (Misc queries) | |||
how do I apply more than 3 conditional formats in excel | Excel Discussion (Misc queries) | |||
How do I set more than 3 conditional formats in Excel | Excel Discussion (Misc queries) | |||
how do i get more than three conditional formats in excel | Excel Worksheet Functions | |||
Conditional Formats in Excel | Excel Worksheet Functions |