automating formats in excel?
Discussion
Having never really used it I'm a complete dunce with excel, wondering if this is even possible, and if so how....
Three cells - D1, E1, F1
If cell D1 has "OS" in it, then all three cells need to be coloured orange.
If cell D1 has a date in it - e.g. "31/05/2007", all three cells need to be blue.
If cell E1 has "PAC" in it, all three cells need to have no fill.
Any simple way of doing this?
Three cells - D1, E1, F1
If cell D1 has "OS" in it, then all three cells need to be coloured orange.
If cell D1 has a date in it - e.g. "31/05/2007", all three cells need to be blue.
If cell E1 has "PAC" in it, all three cells need to have no fill.
Any simple way of doing this?
The function you would use is Format/Conditional Formatting.
Try it, and you will see that you can create a rule along the lines of "If <cell value> is <equal to> <OS> then <set cell shading to green>" This can be very effective. You can create up to three different rules on one cell (so it turns red, green or orange, or remains in the default formatting such as white)
However, I am pretty sure it will only work on that specific cell. So you cannot create a rule that says "If the cell next door is..." I can't think of a way you can do this by another second step, so I don't think you can get exactly what you want. You are probably going to have to live with just column D colour-coding itself.
An option might be to make column A a 'flag' column. You could put a cascading formula in each A cell along the lines of:
"IF D1="OS", THEN "*""
"IF D1 >1, THEN "!"
Then add conditional formatting to Column A: IF * THEN green, IF !, Then Orange, etc.
But it's a lot of complication for little reward.
HTH
Try it, and you will see that you can create a rule along the lines of "If <cell value> is <equal to> <OS> then <set cell shading to green>" This can be very effective. You can create up to three different rules on one cell (so it turns red, green or orange, or remains in the default formatting such as white)
However, I am pretty sure it will only work on that specific cell. So you cannot create a rule that says "If the cell next door is..." I can't think of a way you can do this by another second step, so I don't think you can get exactly what you want. You are probably going to have to live with just column D colour-coding itself.
An option might be to make column A a 'flag' column. You could put a cascading formula in each A cell along the lines of:
"IF D1="OS", THEN "*""
"IF D1 >1, THEN "!"
Then add conditional formatting to Column A: IF * THEN green, IF !, Then Orange, etc.
But it's a lot of complication for little reward.
HTH
Cheers 
having a play going on rich's comments, I've managed to get a cell to change colour dependant on the one next to it - only problem I'm having is how to reference a date - think I need to add another column and have D1 as either empty or a date, rather than empty, a date or "OS".
A bit OTT for what I need really, more of a learning exercise (and to prevent a repeat of last month when I forgot to do something rather important because it wasn't highlighted... oops
)
having a play going on rich's comments, I've managed to get a cell to change colour dependant on the one next to it - only problem I'm having is how to reference a date - think I need to add another column and have D1 as either empty or a date, rather than empty, a date or "OS".
A bit OTT for what I need really, more of a learning exercise (and to prevent a repeat of last month when I forgot to do something rather important because it wasn't highlighted... oops
)Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff