Copy Conditional Formatting Color Scales Excel 2007

I'm using the Conditional Formatting 3-color scale in Excel 2007 to format A1:A3. Excel shows that the format applies to $A$1:$A$3.
When I drag those cells down, or copy down, instead of applying the 3-color scale to B1:B3, or $B$1:$B$3, it applies it to $A$1:$B$3 and changes the conditional format formula "applies to" condition in the original 3 cells to include all 6 cells as well. I tried to remove the $, but Excel automatically puts them back in. Is there a work around for this? I need to apply this conditional format only across the 3 cells in the single row for a few hundred rows.
While I'm new to conditional formatting and this seems like it should be pretty basic, I just can't figure it out.
Thank you for your assistance.

Welcome to the forum catbert,

Try highlighting all the cells you want to apply Conditional Formatting to.

Then enter your format condition as if you are typing it into the top left cell in the selected range. Excel will automatically apply it to all the cells.

If you are still having problems, attach an example, and I can fix it for you.

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

First, I apologize for giving the wrong cell references in my initial post. That being said, I don't actually enter a format condition. I just highlight the 3 cells in the row (A1:C1) and choose the color scale option from the available selections under Conditional Formatting. When I then copy this format down, instead of applying the color scale to the next 3 cells, it applies it to all 6 cells, or all 9 cells, etc., depending on how many rows I've copied it down onto. In the attached example, I manually highlighted and applied the color scale to the 3 cells A-C in Rows 1-3, one row at a time. This is how I want the color scale to be applied, but I'm going to have several hundred rows so this is not an efficient process. When I copied the format via Paste Special from Row 3 to Rows 4 - 9 at the same time, it applied the formatting from A3 to C9 as a whole, instead of keeping each row separate.
I hope this clarifies my problem.

Sorry catbert - still unclear!

Would you please post in an adjacent block what you want it to look like.

Do you want is to be just the three colours, or shades in between? For example, is you aim to have the lowest green, the highest red and the one in the middle yellow?

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi catbert,

Here is a suggestion if you are trying to achieve a scenario where the highest number is red, middle yellow, and lowest green!

Catbert cond formatting.xlsx

If not, please post a table showing what you want.

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Thank you, David. That certainly works for comparing 3 values, but how would I modify that for comparing 4 values that are not in side-by-side columns?

Hi carbert,

It certainly makes for some more complex code, but I would need to see an example.

Please pose another workbook, and it would help if you "dummy up" what you want the result to look like.

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".