Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!!! Conditional Formatting
I have a spreadsheet which is set up to track a schedule based on dates. I
have two rows; top for a projected date (static) - bottom for an actual date (varies). This schedule is tracked daily, therefore I have the TODAY() function in the bottom rows. I would like the following to happen: NOT COMPLETE - NOT AFFECTED: If the bottom "actual date" cell contains the TODAY() function and is equal to or less than the top "projected date" cell, I would like the cell to be formatted white cell/white text. NOT COMPLETE - BEHIND SCHEDULE: If the bottom "actual date" cell contains the TODAY() function and is greater than the top "projected date" cell, I would like the cell to be formatted red cell/red text. COMPLETE - ON SCHEDULE: If the bottom "actual date" cell contains an entered date which is equal to or less than the top "projected date" cell, I would like the cell to be formatted yellow cell/green text. COMPLETE - BEHIND SCHEDULE If the bottom "actual date" cell contains an entered date which is greater than the top "projected date" cell, I would like the cell to be formatted yellow cell/red text. I'm thinking that I have too many variables for conditional formatting. I also attempted to use the AND function within the conditional formatting and was unsucessful. PLEASE help if you can... this schedule is what's driving our company and it's currently a management headache. Any info is appreciated!!! -- Thanks in advance! -T |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!!! Conditional Formatting
I don't know that you'd ever be able to do it using conventional conditional
formatting since in one case you have results based on a cell containing a formula that returns a date (=TODAY()) while in another case you will be evaluating a typed-in date. So the first task is to determine whether that bottom actual date cell contains the =TODAY() formula or not. The two code sections I've provided below will do the job for you, I think. They are attached to the worksheet's events, so to get it all into the proper place: Right-click on the worksheet's name tab and choose [View Code] from the list that comes up. Copy all of the code below and paste it into the code module presented to you. Make changes to the two cell addresses in both code segments to correspond to the cells in question on the actual worksheet. Close the VB Editor. Save the workbook. Try it out. The section associated with the _Activate() event will make sure that the colors are set according to the date relationships when you first choose that sheet (you may have to choose another sheet first to see this - the _Activate() doesn't fire if the sheet is the one selected when the book is first opened or chosen after another workbook has been in use). The section associated with the _Change() event will update the colors when a change to either of the cells takes place - it ignores changes made to any other cells on that sheet or on any other sheet in the workbook. The two sections contain essentially the same code, they just work at different times during your use of the workbook/worksheet. Private Sub Worksheet_Activate() 'change these constants as required 'by using this _Activate() event, we update 'automatically anytime you first look at the sheet Const actualDateCell = "H5" ' address Const projectedDateCell = "D1" ' address Const colorWhite = 2 Const colorRed = 3 Const colorYellow = 50 Const colorGreen = 6 Dim tempFormula As String 'first have to determine if actualDateCell has 'formula "=TODAY()" or something else - 'presumably a typed in date. tempFormula = Range(actualDateCell).Formula If tempFormula = "=TODAY()" Then 'it does have that formula If Range(actualDateCell) <= Range(projectedDateCell) Then 'white on white Range(actualDateCell).Font.ColorIndex = colorWhite Range(actualDateCell).Interior.ColorIndex = xlNone Else 'red on red Range(actualDateCell).Font.ColorIndex = colorRed Range(actualDateCell).Interior.ColorIndex = colorRed End If Else 'presumed to have typed in date If Range(actualDateCell) <= Range(projectedDateCell) Then 'green on yellow Range(actualDateCell).Font.ColorIndex = colorGreen Range(actualDateCell).Interior.ColorIndex = colorYellow Else 'red on yellow Range(actualDateCell).Font.ColorIndex = colorRed Range(actualDateCell).Interior.ColorIndex = colorYellow End If End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) 'change these constants as required 'by using this _SelectionChange() event, we update 'automatically anytime either of the two cells 'of interest change while the sheet is active. 'these two addresses MUST have $ symbols in them Const actualDateCell = "$H$5" ' address Const projectedDateCell = "$D$1" ' address Const colorWhite = 2 Const colorRed = 3 Const colorYellow = 50 Const colorGreen = 6 Dim tempFormula As String 'see if one of our 2 cells changed value If Target.Address < actualDateCell And _ Target.Address < projectedDateCell Then 'not one of our two cells of interest, exit Exit Sub End If 'one or the other changed...continue 'first have to determine if actualDateCell has 'formula "=TODAY()" or something else - 'presumably a typed in date. tempFormula = Range(actualDateCell).Formula If tempFormula = "=TODAY()" Then 'it does have that formula If Range(actualDateCell) <= Range(projectedDateCell) Then 'white on white Range(actualDateCell).Font.ColorIndex = colorWhite Range(actualDateCell).Interior.ColorIndex = xlNone Else 'red on red Range(actualDateCell).Font.ColorIndex = colorRed Range(actualDateCell).Interior.ColorIndex = colorRed End If Else 'presumed to have typed in date If Range(actualDateCell) <= Range(projectedDateCell) Then 'green on yellow Range(actualDateCell).Font.ColorIndex = colorGreen Range(actualDateCell).Interior.ColorIndex = colorYellow Else 'red on yellow Range(actualDateCell).Font.ColorIndex = colorRed Range(actualDateCell).Interior.ColorIndex = colorYellow End If End If End Sub "T. Williams" wrote: I have a spreadsheet which is set up to track a schedule based on dates. I have two rows; top for a projected date (static) - bottom for an actual date (varies). This schedule is tracked daily, therefore I have the TODAY() function in the bottom rows. I would like the following to happen: NOT COMPLETE - NOT AFFECTED: If the bottom "actual date" cell contains the TODAY() function and is equal to or less than the top "projected date" cell, I would like the cell to be formatted white cell/white text. NOT COMPLETE - BEHIND SCHEDULE: If the bottom "actual date" cell contains the TODAY() function and is greater than the top "projected date" cell, I would like the cell to be formatted red cell/red text. COMPLETE - ON SCHEDULE: If the bottom "actual date" cell contains an entered date which is equal to or less than the top "projected date" cell, I would like the cell to be formatted yellow cell/green text. COMPLETE - BEHIND SCHEDULE If the bottom "actual date" cell contains an entered date which is greater than the top "projected date" cell, I would like the cell to be formatted yellow cell/red text. I'm thinking that I have too many variables for conditional formatting. I also attempted to use the AND function within the conditional formatting and was unsucessful. PLEASE help if you can... this schedule is what's driving our company and it's currently a management headache. Any info is appreciated!!! -- Thanks in advance! -T |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!!! Conditional Formatting
Create a simple UDF,
Function IsFormula(rng As Range) IsFormula = rng.HasFormula End Function use the NOT COMPLETE format as standard, and then use =AND(IsFormula(G18),G18$G1) - NOT COMPLETE - BEHIND SCHEDULE =AND(NOT(IsFomula(G18)),G18<=G$1) - COMPLETE - ON SCHEDULE =AND)NOT(IsFormula(G18)),G18G$1) - COMPLETE - BEHIND SCHEDULE -- __________________________________ HTH Bob "T. Williams" wrote in message ... I have a spreadsheet which is set up to track a schedule based on dates. I have two rows; top for a projected date (static) - bottom for an actual date (varies). This schedule is tracked daily, therefore I have the TODAY() function in the bottom rows. I would like the following to happen: NOT COMPLETE - NOT AFFECTED: If the bottom "actual date" cell contains the TODAY() function and is equal to or less than the top "projected date" cell, I would like the cell to be formatted white cell/white text. NOT COMPLETE - BEHIND SCHEDULE: If the bottom "actual date" cell contains the TODAY() function and is greater than the top "projected date" cell, I would like the cell to be formatted red cell/red text. COMPLETE - ON SCHEDULE: If the bottom "actual date" cell contains an entered date which is equal to or less than the top "projected date" cell, I would like the cell to be formatted yellow cell/green text. COMPLETE - BEHIND SCHEDULE If the bottom "actual date" cell contains an entered date which is greater than the top "projected date" cell, I would like the cell to be formatted yellow cell/red text. I'm thinking that I have too many variables for conditional formatting. I also attempted to use the AND function within the conditional formatting and was unsucessful. PLEASE help if you can... this schedule is what's driving our company and it's currently a management headache. Any info is appreciated!!! -- Thanks in advance! -T |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!!! Conditional Formatting
if i'll do it in simple way...i will make another row below the *actual
date*, something like a new row name like *Today()s Status*. In this simple way, you will not confuse the today()s date with the row of *actual date*. hth -- regards, "T. Williams" wrote: I have a spreadsheet which is set up to track a schedule based on dates. I have two rows; top for a projected date (static) - bottom for an actual date (varies). This schedule is tracked daily, therefore I have the TODAY() function in the bottom rows. I would like the following to happen: NOT COMPLETE - NOT AFFECTED: If the bottom "actual date" cell contains the TODAY() function and is equal to or less than the top "projected date" cell, I would like the cell to be formatted white cell/white text. NOT COMPLETE - BEHIND SCHEDULE: If the bottom "actual date" cell contains the TODAY() function and is greater than the top "projected date" cell, I would like the cell to be formatted red cell/red text. COMPLETE - ON SCHEDULE: If the bottom "actual date" cell contains an entered date which is equal to or less than the top "projected date" cell, I would like the cell to be formatted yellow cell/green text. COMPLETE - BEHIND SCHEDULE If the bottom "actual date" cell contains an entered date which is greater than the top "projected date" cell, I would like the cell to be formatted yellow cell/red text. I'm thinking that I have too many variables for conditional formatting. I also attempted to use the AND function within the conditional formatting and was unsucessful. PLEASE help if you can... this schedule is what's driving our company and it's currently a management headache. Any info is appreciated!!! -- Thanks in advance! -T |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!!! Conditional Formatting
Sweet. I'd forgotten about .HasFormula.
For T.Miller - there's one typo in Bob's last conditional formula: that first ) should be ( like in all the others. "Bob Phillips" wrote: Create a simple UDF, Function IsFormula(rng As Range) IsFormula = rng.HasFormula End Function use the NOT COMPLETE format as standard, and then use =AND(IsFormula(G18),G18$G1) - NOT COMPLETE - BEHIND SCHEDULE =AND(NOT(IsFomula(G18)),G18<=G$1) - COMPLETE - ON SCHEDULE =AND)NOT(IsFormula(G18)),G18G$1) - COMPLETE - BEHIND SCHEDULE -- __________________________________ HTH Bob "T. Williams" wrote in message ... I have a spreadsheet which is set up to track a schedule based on dates. I have two rows; top for a projected date (static) - bottom for an actual date (varies). This schedule is tracked daily, therefore I have the TODAY() function in the bottom rows. I would like the following to happen: NOT COMPLETE - NOT AFFECTED: If the bottom "actual date" cell contains the TODAY() function and is equal to or less than the top "projected date" cell, I would like the cell to be formatted white cell/white text. NOT COMPLETE - BEHIND SCHEDULE: If the bottom "actual date" cell contains the TODAY() function and is greater than the top "projected date" cell, I would like the cell to be formatted red cell/red text. COMPLETE - ON SCHEDULE: If the bottom "actual date" cell contains an entered date which is equal to or less than the top "projected date" cell, I would like the cell to be formatted yellow cell/green text. COMPLETE - BEHIND SCHEDULE If the bottom "actual date" cell contains an entered date which is greater than the top "projected date" cell, I would like the cell to be formatted yellow cell/red text. I'm thinking that I have too many variables for conditional formatting. I also attempted to use the AND function within the conditional formatting and was unsucessful. PLEASE help if you can... this schedule is what's driving our company and it's currently a management headache. Any info is appreciated!!! -- Thanks in advance! -T |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP!!! Conditional Formatting
Bob, not sure if anyone has ever told you this, but... YOU'RE THE MAN!!!
This worked exactly how I wanted it to. Thank you so much, I really appreciate it! -- Thanks! -T "Bob Phillips" wrote: Create a simple UDF, Function IsFormula(rng As Range) IsFormula = rng.HasFormula End Function use the NOT COMPLETE format as standard, and then use =AND(IsFormula(G18),G18$G1) - NOT COMPLETE - BEHIND SCHEDULE =AND(NOT(IsFomula(G18)),G18<=G$1) - COMPLETE - ON SCHEDULE =AND)NOT(IsFormula(G18)),G18G$1) - COMPLETE - BEHIND SCHEDULE -- __________________________________ HTH Bob "T. Williams" wrote in message ... I have a spreadsheet which is set up to track a schedule based on dates. I have two rows; top for a projected date (static) - bottom for an actual date (varies). This schedule is tracked daily, therefore I have the TODAY() function in the bottom rows. I would like the following to happen: NOT COMPLETE - NOT AFFECTED: If the bottom "actual date" cell contains the TODAY() function and is equal to or less than the top "projected date" cell, I would like the cell to be formatted white cell/white text. NOT COMPLETE - BEHIND SCHEDULE: If the bottom "actual date" cell contains the TODAY() function and is greater than the top "projected date" cell, I would like the cell to be formatted red cell/red text. COMPLETE - ON SCHEDULE: If the bottom "actual date" cell contains an entered date which is equal to or less than the top "projected date" cell, I would like the cell to be formatted yellow cell/green text. COMPLETE - BEHIND SCHEDULE If the bottom "actual date" cell contains an entered date which is greater than the top "projected date" cell, I would like the cell to be formatted yellow cell/red text. I'm thinking that I have too many variables for conditional formatting. I also attempted to use the AND function within the conditional formatting and was unsucessful. PLEASE help if you can... this schedule is what's driving our company and it's currently a management headache. Any info is appreciated!!! -- Thanks in advance! -T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |