Hi, I have several columns of data. Say column A to column N. Now each column will have 10 data (figures) from A1 to N10 where each column represents a month's worth of 10 important data.
I was trying to use conditional formatting to hi-light just two colors but I can't get CF to work out.
Situation: column A is the starting month. so no formating is needed. But from columns B to N there will be CF. This is what I did. I hi-light B1:B10. Goes to CF and set up 2 conditions.
condition 1: if B1 is less than A1, then B1 is yellow.
condition 2: if B1 is greater or equals to A1, then B1 is yellow.
And this would apply from B2 to B10 comparing against the corresponding cells in column A.
When I select B1:B10 in CF, I went to "Format only cells that contain" -> "Cell value" -> "less than" -> =$A$1:$A$10 and format the cell to yellow and hit "OK". Excel gave me an error message "You cannot use a direct reference to a worksheet range in a Conditional Formatting formula. Change the reference to a single cell, or use the reference with a worksheet function, such as =sum(A1:E5)"
I don't understand this. I thought Excel would understand that I was trying to compare B1 against A1, B2 against A2 and so on until B10 against A10 (green = increase/same from one month to the next and yellow = decrease from one month to the next)
I thought about using format painter but it doesn't work. I tried removing the $ sign keep coming back to compare against $A$1 but I don't want any other cells other than B1 comparing against A1 (C1 vs B1; D1 vs C1).
I also tried "Use a formula to determine with cells to format" and set the formula to
=$B$1:$B$10>=$A$1:$A$10 to green and =$B$1:$B$10<$A$1:$A$10 to yellow but it didn't work as well.
There must be something I'm not doing right. Please kindly advise. Thanks!
I was trying to use conditional formatting to hi-light just two colors but I can't get CF to work out.
Situation: column A is the starting month. so no formating is needed. But from columns B to N there will be CF. This is what I did. I hi-light B1:B10. Goes to CF and set up 2 conditions.
condition 1: if B1 is less than A1, then B1 is yellow.
condition 2: if B1 is greater or equals to A1, then B1 is yellow.
And this would apply from B2 to B10 comparing against the corresponding cells in column A.
When I select B1:B10 in CF, I went to "Format only cells that contain" -> "Cell value" -> "less than" -> =$A$1:$A$10 and format the cell to yellow and hit "OK". Excel gave me an error message "You cannot use a direct reference to a worksheet range in a Conditional Formatting formula. Change the reference to a single cell, or use the reference with a worksheet function, such as =sum(A1:E5)"
I don't understand this. I thought Excel would understand that I was trying to compare B1 against A1, B2 against A2 and so on until B10 against A10 (green = increase/same from one month to the next and yellow = decrease from one month to the next)
I thought about using format painter but it doesn't work. I tried removing the $ sign keep coming back to compare against $A$1 but I don't want any other cells other than B1 comparing against A1 (C1 vs B1; D1 vs C1).
I also tried "Use a formula to determine with cells to format" and set the formula to
=$B$1:$B$10>=$A$1:$A$10 to green and =$B$1:$B$10<$A$1:$A$10 to yellow but it didn't work as well.
There must be something I'm not doing right. Please kindly advise. Thanks!
