Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to set cell color
I have a column containing a sorted list of values which includes duplicate
values such as: Row 1: 1 Row 2: 1 Row 3: 2 Row 4: 3 Row 5: 3 Row 6: 4 Row 7: 4 Row 8: 4 Row 9: 5 Without using VBA or macros (an unfortunately non-negotiable requirement of the customer) I'm tasked to do the following using conditional formatting: 1. Make a cell that has the same value as the cell immediately above it the same color as that of the cell immediately above it 2. Make the color of a cell whose value is not the same as the cell immediately above it a different color than the cell immediately above it 3. Alternating two colors is fine For my example data, the cells in rows 1 and 2 should be yellow, row 3 should be blue, row 4 and 5 should be yellow, row 6 through 8 should be blue, and row 9 should be yellow. Using Excel 2003 and just formulas, I don't think that this is possible but I'm hoping that someone out there will know better. I don't know if Excel 2007 has added something to make this possible. Anyone know of a way to do this? Brian |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to set cell color
I'm going to step out on a limb and say I don't think it can be done with
just conditional formatting either. The conditional format doesn't know what color the previous cells are. I can set up conditional format with formulas that will change colors of cells with contiguous groups, but not in alternating fashion. In your series of data, assuming we default shade things to Yellow and switch to blue when we have a contiguous group, then: Row 1: 1 - changed to blue Row 2: 1 - changed to blue Row 3: 2 - no change, YELLOW Row 4: 3 - changed to blue Row 5: 3 - changed to blue Row 6: 4 - changed to blue Row 7: 4 - changed to blue Row 8: 4 - changed to blue Row 9: 5 - no change, YELLOW and I fully realize that's not what you want. You can come close by setting up formulas that will change the colors of all but the 1st of a series. Choose row 2 to the end of the list, and in Conditional Formatting Cell Value Is "equal to" A1 and set the alternative format. That gives us: Row 1: 1 - no change, YELLOW Row 2: 1 - changed to blue Row 3: 2 - no change, YELLOW Row 4: 3 - no change, YELLOW Row 5: 3 - changed to blue Row 6: 4 - no change, YELLOW Row 7: 4 - changed to blue Row 8: 4 - changed to blue Row 9: 5 - no change, YELLOW "BrianB" wrote: I have a column containing a sorted list of values which includes duplicate values such as: Row 1: 1 Row 2: 1 Row 3: 2 Row 4: 3 Row 5: 3 Row 6: 4 Row 7: 4 Row 8: 4 Row 9: 5 Without using VBA or macros (an unfortunately non-negotiable requirement of the customer) I'm tasked to do the following using conditional formatting: 1. Make a cell that has the same value as the cell immediately above it the same color as that of the cell immediately above it 2. Make the color of a cell whose value is not the same as the cell immediately above it a different color than the cell immediately above it 3. Alternating two colors is fine For my example data, the cells in rows 1 and 2 should be yellow, row 3 should be blue, row 4 and 5 should be yellow, row 6 through 8 should be blue, and row 9 should be yellow. Using Excel 2003 and just formulas, I don't think that this is possible but I'm hoping that someone out there will know better. I don't know if Excel 2007 has added something to make this possible. Anyone know of a way to do this? Brian . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to set cell color
On Apr 23, 1:36*pm, JLatham wrote:
I'm going to step out on a limb and say I don't think it can be done with just conditional formatting either. *The conditional format doesn't know what color the previous cells are. *I can set up conditional format with formulas that will change colors of cells with contiguous groups, but not in alternating fashion. *In your series of data, assuming we default shade things to Yellow and switch to blue when we have a contiguous group, then: Row 1: *1 - changed to blue Row 2: *1 - changed to blue Row 3: *2 - no change, YELLOW Row 4: *3 - changed to blue Row 5: *3 - changed to blue Row 6: *4 - changed to blue Row 7: *4 - changed to blue Row 8: *4 - changed to blue Row 9: *5 - no change, YELLOW and I fully realize that's not what you want. *You can come close by setting up formulas that will change the colors of all but the 1st of a series. * Choose row 2 to the end of the list, and in Conditional Formatting Cell Value Is * * "equal to" * *A1 and set the alternative format. *That gives us: Row 1: *1 - no change, YELLOW Row 2: *1 - changed to blue Row 3: *2 - no change, YELLOW Row 4: *3 *- no change, YELLOW Row 5: *3 - changed to blue Row 6: *4 - no change, YELLOW Row 7: *4 - changed to blue Row 8: *4 - changed to blue Row 9: *5 - no change, YELLOW "BrianB" wrote: I have a column containing a sorted list of values which includes duplicate values such as: Row 1: *1 Row 2: *1 Row 3: *2 Row 4: *3 Row 5: *3 Row 6: *4 Row 7: *4 Row 8: *4 Row 9: *5 Without using VBA or macros (an unfortunately non-negotiable requirement of the customer) I'm tasked to do the following using conditional formatting: 1. Make a cell that has the same value as the cell immediately above it the same color as that of the cell immediately above it 2. Make the color of a cell whose value is not the same as the cell immediately above it a different color than the cell immediately above it 3. Alternating two colors is fine Assuming you are in A1 to A9 Set B1 value to "Yellow", in B2 use : =IF(A2=A1,IF(B1="Yellow","Yellow","Blue"),IF(B1="Y ellow","Blue","Yellow")) Extend this down to B9. Conditional format A1:AX with two rules: 1: formula =if(b1="Yellow",1,0) -- format with yellow fill 2: formula =if(b2="Blue",1,0) -- format with blue fill You would have to hide column B (or any column on the same or another perhaps hidden page could be used) Not the most elegant method, but it works. TK For my example data, the cells in rows 1 and 2 should be yellow, row 3 should be blue, row 4 and 5 should be yellow, row 6 through 8 should be blue, and row 9 should be yellow. Using Excel 2003 and just formulas, I don't think that this is possible but I'm hoping that someone out there will know better. I don't know if Excel 2007 has added something to make this possible. Anyone know of a way to do this? Brian . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to set cell color
"Cortez" wrote in message
... On Apr 23, 1:36 pm, JLatham wrote: I'm going to step out on a limb and say I don't think it can be done with just conditional formatting either. The conditional format doesn't know what color the previous cells are. I can set up conditional format with formulas that will change colors of cells with contiguous groups, but not in alternating fashion. In your series of data, assuming we default shade things to Yellow and switch to blue when we have a contiguous group, then: Row 1: 1 - changed to blue Row 2: 1 - changed to blue Row 3: 2 - no change, YELLOW Row 4: 3 - changed to blue Row 5: 3 - changed to blue Row 6: 4 - changed to blue Row 7: 4 - changed to blue Row 8: 4 - changed to blue Row 9: 5 - no change, YELLOW and I fully realize that's not what you want. You can come close by setting up formulas that will change the colors of all but the 1st of a series. Choose row 2 to the end of the list, and in Conditional Formatting Cell Value Is "equal to" A1 and set the alternative format. That gives us: Row 1: 1 - no change, YELLOW Row 2: 1 - changed to blue Row 3: 2 - no change, YELLOW Row 4: 3 - no change, YELLOW Row 5: 3 - changed to blue Row 6: 4 - no change, YELLOW Row 7: 4 - changed to blue Row 8: 4 - changed to blue Row 9: 5 - no change, YELLOW "BrianB" wrote: I have a column containing a sorted list of values which includes duplicate values such as: Row 1: 1 Row 2: 1 Row 3: 2 Row 4: 3 Row 5: 3 Row 6: 4 Row 7: 4 Row 8: 4 Row 9: 5 Without using VBA or macros (an unfortunately non-negotiable requirement of the customer) I'm tasked to do the following using conditional formatting: 1. Make a cell that has the same value as the cell immediately above it the same color as that of the cell immediately above it 2. Make the color of a cell whose value is not the same as the cell immediately above it a different color than the cell immediately above it 3. Alternating two colors is fine Assuming you are in A1 to A9 Set B1 value to "Yellow", in B2 use : =IF(A2=A1,IF(B1="Yellow","Yellow","Blue"),IF(B1="Y ellow","Blue","Yellow")) Extend this down to B9. Conditional format A1:AX with two rules: 1: formula =if(b1="Yellow",1,0) -- format with yellow fill 2: formula =if(b2="Blue",1,0) -- format with blue fill You would have to hide column B (or any column on the same or another perhaps hidden page could be used) Not the most elegant method, but it works. TK Looks good, TK. I'll give it a try. Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to set color in a cell | Excel Worksheet Functions | |||
Cell color formula | Excel Discussion (Misc queries) | |||
Formula Cell color limitation | Excel Discussion (Misc queries) | |||
color a cell with a formula in it? | Excel Worksheet Functions | |||
need formula to add cell sums by color of cell | Excel Discussion (Misc queries) |