There are no specific rules other than those that apply to normal formulas and how they are referenced. The default is both $ signs, as it is assumed that the reference is to an absolute cell. If you want the column to be unchanged for cells in the range you put the $ before the A. If you want the row unchanged you put the $ before the number. It is not a question of before or after, the $ sign goes before the element it applies to. The sign might appear as though it is after the letter, but that is only because it is before the number.
The $ means that the element it applies to will not change if the format is copied., i.e. if the column is A, and is preceded by a $, then all cells refer to column A. Similarly with rows.

One use for relative (no $ at all) conditional formatting is to use it to highlight duplicate rows in a sorted list. If all cells in the list are highlighted you know you have an exact match.

Hope that clarifies rather than confuses,

Andrew C

Hi Folks,

I almost never use conditional formatting except in the following manner:

1) Select one cell in the range-to-be-formatted
2) Set the conditions using the conditional format dialog
3) Selectively getting rid of “absolutes” ($)
4) Copy the formatting to the other cells in the range

This simple technique takes much better advantage of the power of conditional formatting, and it took me a while to figure it out.

JIM

Oops. I guess there is a better way to describe what I was trying to describe. Here is “try #2”.

It is easier to think in terms of one cell when it comes to conditional formatting. Therefore, set the conditions for one cell, then Copy, and Paste Special the formats. Then, inspect the conditional format of one of the other cells you copied to, and decide if it is adequate. If not, figure out why not, and why the Copy and Paste Special didn;t work. Usually, it has to do with the absolute notations in teh original conditions. Then, go back to the first one, selectively remove the $, Copy and Paste Special again.

I have difficulty following verbal descriptions of such things, but I could probably help of you could work up a sample and post it here, with a description of what you want to happen, and what is happening isntead that you don’t want.

Jimbo is fine!

Hi,
This isn’t always very practical but you can use conditional formatting based on the value in a cell in another sheet if you name that cell first. For example, if you define a name ‘test’ that refers to cell A1 on sheet1, then on sheet2 you can add a conditional format =test=4 to a cell and that will work, even though =’sheet1′!A1=4 won’t work.
Hope that helps.

True indeed. Because of my typical application of conditional formatting (changing the display of individual cells in a range based on their corresponding cells in another range), using named ranges doesn’t work. I resort to setting the conditions for one cell, with a relative reference, then copying the format to the other cells. It’s an important but subtle distinction, and it goes against my usual strategy of using named ranges whenever possible.

Jim,
Now that I think more about it, if you define the name as a relative reference (eg ‘test’ =’Sheet1′!A1), then you can use this on a different worksheet and it will refer to the corresponding cell on Sheet1 – i.e. if you use it in cell A2 on sheet2, it refers to cell A2 on sheet1. This does mean that you can use it to conditionally format a range on one sheet based on the values of the same range on another sheet (if you see what I mean)
FWIW.