Conditional Formatting Issues!!

[bryanb72]

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!!
<!-- google_ad_section_end -->

 

[BAlGaInTl]

Conditional formatting doesn't have to be absolute. Just take out the $ in your rule so that it reads $A10 instead of $A$10.

 

[bryanb72]

Yeah, I've been through this on another message board and it started the same way. Excel returns an error when taking them $ out, noting that it will not use relative references. It's the reference cells that need to be in sequence as the conditionally formatted cells are easy to group. Therefore, attempting to get cell E15 to display an up arrow icon if it is greater than D15 works fine. However, when you copy the conditional formatting of E15 to E16, it still references D15 instead of D16, and so on.
Thanks.

 

[barry houdini]

Try using this formula to reference the value on the current row without using relative references

=INDEX($D:$D,ROW())

 

[barry houdini]

Actually, coming back to this and testing more thoroughly, I realise that doesn't work as I thought it did

I may be wrong but I don't think you can do this with icon sets if you want the comparison values to be variable. You may need to use "Use a formula to determine which cells to format" and just use =E15>D15 or similar, with a simple fill colour or font colour......

 

[bryanb72]

Yeah, that's a bummer...I'm hoping I don't have to do each cell separately! Thanks.