Conditional formatting autofilling to adjacent column (screenshot enclosed)

[Noumenon]

In my miles per gallon spreadsheet, the price of gas column has the automatic red-to-green conditional formatting, but when I enter my total miles, it thinks it's part of that range and formats it.

 

[T. Valko]

In my miles per gallon spreadsheet, the price of gas column has the automatic red-to-green conditional formatting, but when I enter my total miles, it thinks it's part of that range and formats it.

Maybe all you need to do is clear the formats for that range of cells.

I keep this button on my QAT.

Select the range of cells in question
Goto the Home tab
In the Editing Group, select Clear (the eraser icon)>Clear Formats

 

[Noumenon]

Thanks, that didn't work for me. Just now I tried clearing the formats on the gallons column and setting up a brand new red-to-green conditional format, but I get the same problem.

One thing you can't see in the screen shot is that there are blank cells in both columns. If I enter a new number in a blank cell higher up the miles column, it gets conditional formatted. If I change a number in one of the filled cells, it does not get formatted. If I delete a filled cell and re-enter the number, it gets formatted. So only blank cells are getting the formatting from the column to their left.

 

[Noumenon]

This is Excel 2007, I forgot to say (sorry, didn't see if there are posting rules). And I would have posted the .xlsx file except the FAQ seemed to say the download that lets you do that is off the Web.

 

[Noumenon]

To be clearer about my #3: I did try clearing the format and it did work as far as having the formatting restricted to the one column, but then when I entered another new number it was formatted again, even if I cleared the formatting in that cell before entering a number in it.

 

[T. Valko]

To be clearer about my #3: I did try clearing the format and it did work as far as having the formatting restricted to the one column, but then when I entered another new number it was formatted again, even if I cleared the formatting in that cell before entering a number in it.

Well, at this point I'd need to see the file to try to figure out what's what.

Can you post a link to your file? You can use a free file host if need be.

 

[Noumenon]

I've never used Dropbox's public folder before, but this should work.

 

[T. Valko]

I've never used Dropbox's public folder before, but this should work.

Ok, I have your file in front of me.

There is no formatting applied to column E. There is formatting applied to columns D and F, but not E.

 

[Noumenon]

I'm glad the file upload worked, it will eliminate a lot of confusing descriptions. My problem, simply stated, is that if you enter a number in a blank cell in column E that has a filled cell to the left of it, it gains the formatting from column D.

 

[T. Valko]

I'm glad the file upload worked, it will eliminate a lot of confusing descriptions. My problem, simply stated, is that if you enter a number in a blank cell in column E that has a filled cell to the left of it, it gains the formatting from column D.

Hmmm...

Ok, now I see what you mean.

It seems to happen to every consecutive cell in the row even though none of the other columns has a CF rule applied.

For example, in the sample file cell D9 contains an entry and the rest of the row is empty. D9 and F9 have CF applied. If you start entering numbers in E9 then F9 then G9 then H9 then I9 then etc., etc. all these cells get formatted yet the only cells that had a pre-defined CF rule applied are D9 and F9.

To stop this check this setting:

Office button
Excel Options
Advanced
Editing Options
Uncheck: Extend data range formats and formulas

They sure did screw up the CF tool in Excel 2007. It's a good thing that they greatly expanded the number and types of formatting that could be applied but at the same time they totally screwed up the CF user interface. It sucks big time!