Conditional cell formatting not working properly

I have the following condition for formatting a range of cells (turning them red): =AND(BE314="S",BS314=5), and the range of cells I'm trying to format is: =$BW$314:$CC$332

If I write the condition using $ signs like this: =AND($BE$314="S",$BS$314=5), the formatting of $BW$314:$CC$332 works fine, but as soon as I remove the $ signs and write it like this =AND(BE314="S",BS314=5), only cell BW314 is formatted, and the rest of the range is not. I'd like to leave the $ signs off b/c I need to paste this formatting to multiple areas in the workbook, and have it be depended not on =AND($BE$314="S",$BS$314=5), but whatever row and column this condition ends up in when I copy and paste the range of cells I am formatting.

So for example, if I copy $BW$314:$CC$332 that has been conditionally formatted and paste it 100 rows down to $BW$414:$CC$432, I'd like the condition to automatically be AND(BE414="S",BS414=5) without me having to go in and changing where that condition is located.

Is this possible to do?

We might need a sample workbook illustrating the problem. I cannot replicate the problem. I can use relative referencing without difficulty in conditional formatting.

Are BE314 and BS314 specific single cells that need to be referenced, or are they part of a range that you are testing against?

If single cells, then you need to keep the $, but if they are a range (in those columns), then perhaps try $BE314 and $BS314

Please see my next post

Ok, thank you for taking a look at it. Here's an example of what I am trying to achieve in the sheet attached: when I copy the blue square located in D10:E14 to D20:E24, I'd like the condition that makes it blue be in A20 and B20 automatically without me having to select the new square and rewrite the condition from =AND($A$10=5,$B$10="S") to =AND($A$20=5,$B$20="S").

OK so what do you want, the full fill or just the cell?

I'd like the rectangle in cells D20:E24 to be fully filled blue if A20=5 and B20="S", but need the rectangle to have this conditional reference to A20 and B20 as soon as I copy it over from above.

OK, you cant have your cake and eat it.
If you lock the cells so that they always reference A10 and B10 for the 1st block, then when you copy, those are the references that copy over too.
If the criteria would always be in the same relative position (stays in same row, 3 columns to the left) you could maybe use OFFSET()

Too bad.... They are always in the same relative position. Could you give an example of how to use OFFSET() in this case?

hmm was trying this, but it doesnt seem to "carry"...
=AND(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN(),1,1)),0,-3)=5,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN(),1,1)),0,-2)="S")

OK, thanks for trying. Any other suggestions?

I have a suggestion, but you are probably not going to like it. It seems to me that a spreadsheet works best when things are copied as rows or columns, but I, too, have run into troubles when trying to copy things in "blocks" like this. I tend to work in small enough "blocks" that I will build the block with relative references one cell at a time. So, apply a conditional formatting rule to D10 that is AND(A10=5,B10="S"), D11 has the same formula, and so on through the 10 cells in D10:E14. It was tedious to set up the block like this, but now the block can be copied around fairly easily.

I would expect that Ford's suggestion to use the OFFSET() function would work -- it is just a matter of really thinking through the logic of "from the current cell, how many cells to the left and up do I need to go to find the upper left cells in the block". Which means the formula needs a way to know where in the block it is. I have not thought it through fully, but I will think on it and see what comes of it.

MrShorty, as you can see, I was trying that. If you put that is a cell and copy down and across, it provides the required TRUE for the CF, but when you put in as a rule, it wont apply.

	A	B	C	D	E	F	G	H
9							$D$10
10	5	S		TRUE			TRUE	TRUE
11							TRUE	TRUE
12							TRUE	TRUE
13							TRUE	TRUE
14							TRUE	TRUE

Because I could not use the ADDRESS function, I just put the starting reference in G9 and referenced it, as if it was teh ADDRESS result
G10=AND(OFFSET(INDIRECT($G$9),0,-3)=5,OFFSET(INDIRECT($G$9),0,-2)="S")
copied down and across