Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Thank you in advance for any help you may be able to provide!
I would like to make the backgroud of a cell change color based on the cells relaton to another cell. For example; The celles in column A are formated a Number/Custom/h.mm The celles in column B are formated a Number/Custom/h.mm I would like for the background of the cell in column B to be Blue if the time in B is more then 30 minutes before the time in column A. I would like for the background of the cell in column B to be white if the time in B is less then 30 minutes before the time in column A and less then 1 hour after then the time in column A. I would like for the background of the cell in column B to be Red if the time in B is more then 1 hour after the time in column A. Thank you again. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Hi
May not be the best solution, but the problem is time calculations resulting in a minus. Insert a helper column, say C. Insert the following formula: =A6/1*24-B6/1*24. Results will show as decimal, eg 30 minutes will show as 0.50 With the cursor on the cell in col B, click on Format, Conditional Formatting, and insert the following 3 conditions. Change "Cell Value Is" to "Formula is" for all three. Enter the following 3 formulae, for conditions 1, 2 and 3 respectively: 1 = =C9=0.5 2 = =AND(C9<0.5,C9-1) 3 = =C9<-1 For the conditions, set Format|Patterns to the colours you want, eg Condition 1 = Green, Condition 2 is Yellow and Condition 3 is Red. If you want to keep the normal background for condition 1, don't use condition 1. You can then hide the helper column, obviously after copying the formula to all the relevant cells in Column C. " wrote: Thank you in advance for any help you may be able to provide! I would like to make the backgroud of a cell change color based on the cells relaton to another cell. For example; The celles in column A are formated a Number/Custom/h.mm The celles in column B are formated a Number/Custom/h.mm I would like for the background of the cell in column B to be Blue if the time in B is more then 30 minutes before the time in column A. I would like for the background of the cell in column B to be white if the time in B is less then 30 minutes before the time in column A and less then 1 hour after then the time in column A. I would like for the background of the cell in column B to be Red if the time in B is more then 1 hour after the time in column A. Thank you again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |