# Conditional Formatting Issues!!



## bryanb72 (Feb 25, 2009)

Hi guys, 

I'm using Excel 2007 and am having difficulty with conditional formatting using the "format cells based on their values" function. I am trying to copy the conditional formatting to the cells below, referencing the next cell in sequence as I go. I am trying to do this for hundreds of cells so formatting one by one is not desireable. However, since the formatted cells require that the reference cell be absolute ($A$13), I am unable to copy the first cell down to the bottom with excel autimatically chaniging the referecne cell to the next cell in sequence. For example, I am asking $B10 to reference $A$10 and return a conditional value based o nthe number in $A$10. When I copy this down, I want the formatting to automatically change in sequence: B11, A11; B12, A12, etc...All of the forums I have been reading have not addresed this issue. Rather, they address the issue of grabbing a group of cells in a column (which is easy) but, when copied, they all reference the first cell, in this case A10. So, A10 - A300 all have the conditional formatting, but they all reference B10, not B10 - B300. I hope this makes sense. Basically, I'm asking how to copy absolute values down a given column and having the reference cells advance in number as I run down the spreadsheet.
THANKS!!


----------



## Aj_old (Sep 24, 2007)

In conditional formatting cell reference does not necessarily need to be absolute, so you can use A13 instead of $A$13


----------



## bryanb72 (Feb 25, 2009)

Aj-old, thanks for the response. However, when using the "format cells based on their values" function in 2007, Excel requires that the reference cell be absolute. The function cell does not require an absolute value, but the reference cell does. Perhaps there is another function that I can use?


----------



## Aj_old (Sep 24, 2007)

why do you use the "format cells based in their values"?


----------



## bryanb72 (Feb 25, 2009)

I could use another function, however, I am hoping to use the icon functions. In this case, a lower cell value in A10 vs B10 would produce a "down arrow" icon, graphically showing a "lower value". Higher would produce an up arrow, etc... If there is another way to produce this effect, I'm open to it.


----------



## Bianca.meldoy (Feb 21, 2009)

I tried setting up a conditional format using the wizard. Yes it does put the $ in front of the cell, but you can delete it/ them out. (When you set it up go back in to the rule and delete the $ out) I tested it out and was able to accomplish a simple copy and paste. Then tried it again with a paste special format. It too worked. 

But all I did was have cell and text be a specific color if it was between the numbers of 4 and 12. Nothing convoluted. 

Hope this helps you and if it does, AJ_Old should get the props.


----------



## Bianca.meldoy (Feb 21, 2009)

tried it again with the arrows and using the parameters you have given. It worked.


----------



## bryanb72 (Feb 25, 2009)

Bianca.meldoy, thanks for the post. Forgive me, but I can't find a wizard for conditional formatting. Did the wizard use the "format cells based on their values"?
Thanks.

P.S I just tried it and the error came up "you cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets".


----------



## Bianca.meldoy (Feb 21, 2009)

No problem. Go to Home tab, condtional formating icon, thean manage rules. This will bring up your already set up rule. In there you will be able to delete the $ out of the formula. Hope this helps you.


----------



## bryanb72 (Feb 25, 2009)

I'm using Office 2007, perhaps this is causing an issue? I am still getting the same error as stated above. All the conditional cells are still referencing 1 single cell.


----------



## Bianca.meldoy (Feb 21, 2009)

Could you post the formula you are using for this? I have 2007 also. and it can be "fun". you should be able to go into the manage rules option and manually change the formula to what you need it to say.


----------



## bryanb72 (Feb 25, 2009)

Thanks Bianca.meldoy, 

I know!! I'd love it to be fun! I'm using the "Icon Sets", number function in the "type" drop down, cell $F$15 in the "value" box. Value is > the adjacent (reference) cell $E$15. When I delete the absolute value $, Excel returns an error.
Thanks!


----------



## Bianca.meldoy (Feb 21, 2009)

ROW 1 MTD ACTUAL BUDGET DIFFERENCEROW 2 120 120 130 -10 Say you want the 3-Light Conditional Formatting Formula to be used on the MTDColumn. Make sure you're using the "formula is" not "cell value is" callout.
In your first cell in b2, the three formulas would then be:
Green Light =D2>=0
Yellow Light =D2>=-3
Red Light =D2<-3
Not: Green Light >= =D2=0Yellow Light >= =D2=(-3)Red Light =D2 <-3 
Can't take credit for this one either. I don't remember who or where I found it, but it helped me to figure out what I needed to do to fix the one I was working on. 

I also have a website you might find useful. It is a pdf file for Excel 2007. You will need Adobe Reader for it, but what the hey. Since it is free and so is the pdf it might be helpful. Just in case, I am not throwing you to the waves of cyper space. Just thought you might be interested in this.

ftp://ftp.bookpool.com/sc/39/0470044039.pdfftp://ftp.bookpool.com/sc/39/0470044039.pdf


----------



## bryanb72 (Feb 25, 2009)

Thanks Bianca.meldoy, 

I tried that and it looks like it may work, but I don't think I fully understand your note about "formula is" vs "cell value is". If I don't click "format all cells based on their values", I can't get the 3-light icon option...So, I'm not sure which options to use. Are you still in the Wizard? If so, would you mind posting the option callouts? Also, I only get 2 formatting choices so the "Red Light" in your formula doesn't apply?? 

I'm using, New Rule, format all cells based on their values, Icon Sets, 3-arrows (Gray), Type = number (using your example if D2 is MTD; "when value is" >F2 (BUDGET)). I'm asking the program to give me an up arrow if BUDGET is higher than MTD, Down arrow if less than...

Thanks...


----------



## Bianca.meldoy (Feb 21, 2009)

When you go into rule manager, click on the edit rule button located on the upper bar. Once you are in there you can select a rule type I chose Format all cells based on their values. (I know what I said, but this is the first step) Don't panic if you use the drop down menu and you see your icon info has gone away. It's still there in the drop down menu located in the Edit the rule description. In there you can choose how you want your arrows to act. 

The Type can be set to value, formula, number or percentage (I use that one for my budget sheet) The first two are your options, the third one will automatically default to the only option left. For example, my up arrow is set at > or = 67 (>=) my nutral arrow is >= 33. Excel took < 33 for the down arrow. 

Hope this helps


----------



## bryanb72 (Feb 25, 2009)

Good morning Bianca.Meldoy - thanks for all the help, but it's still not working. The values 67 and 33 are just random input values that tell the formatting if it's higher than this, up arrow, or lower than this, down arrow...thanks for all the help. I'm still trying to get the reference to be a CELL, not just a number. I WISH it were that easy!


----------



## slurpee55 (Oct 20, 2004)

I think that the reference needs to be absolute only to the column, but not the row, such as =$C1


----------



## bryanb72 (Feb 25, 2009)

Thanks Slurpee55, unfortunately not. It wants both values to be absolute. It just doesn't make sense that Excel won't allow any work-around for this. To format 300 cells with with the same condition using the menu's one by one is ridiculous!


----------



## slurpee55 (Oct 20, 2004)

I only have 2007 at home - can't help here. Good luck....


----------



## slurpee55 (Oct 20, 2004)

Okay, I just tried this - I hope it matches what you are wanting to do.
I set up two columns - in A they are regularly increasing, in B they are random.
Then I highlighted all of A, went to conditional formatting and entered as Formula Is
=A1<B1
Since the whole column was highlighted, this was applied, but the cells farther down match the rows, e.g. 
=A13<B13
is the conditional formula for cell A13.
You have been trying with formatting based on the cell value - try it with a formula.


----------



## bryanb72 (Feb 25, 2009)

Slurpee - That works PERFECTLY!! Except that I'm trying to use the conditional formatting to not only give and up or down value, but also to color / insert an icon the conditionally formatted cell!! The error reads "you cannot use relative references in conditional formatting criteria for color scales, data bars or Icon sets. Darnit!! I'm still trying!! heck, by now I could have input them all one by one!!


----------



## slurpee55 (Oct 20, 2004)

According to MS, with regard to formatting using an icon set, this is what they say

Format a formula result Select Formula, and then enter a formula in each Value box.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

So, 1), make sure you have formulas in all, and have them start with an equals sign.
Then, 2) test the formulas in the sheet to see if they have problems.


----------

