Keep cells in a column from highlighting

[tool4daman]

Hi,
I have a column conditionally formatted to highlight in green if the cell is a positive value (>.01) and red if it is a negative value (<.01). That works well if I manually enter data into each cell.

But when I enter a formula into the cells in this column, they automatically highlight green even though the value is 0. I can't seem to figure out how to stop it. I'm talking about column L. Specifically, cell L11. I want cell L11 to be blank unless there is data in cell J11. But when I enter the formula, it highlights green. I've tried setting another highlight rule that if the cell = 0, to not fill. While that works, it removes the light blue highlight that the table gives every other row. And I'd like to keep every other row highlighted in light blue (with the exception of this column).

I've attached the spreadsheet here. I'd appreciate any help someone can give me on this.

Thank you.

[jeffreybrown]

Hello & Welcome to the Forum,

Delete the red and green conditions you currently have and replace with:

=AND(LEN(L2)>0,L2>=0.01) >> Green

=AND(LEN(L2)>0,L2<=-0.01) >> Red

Applies to: =$L$2:$L$16

What about 0?

[Pepe Le Mokko]

L11 contains a null string, which is text and larger than a number ( I can explain why if need be)
Add a condition like =$L2="" before the other two and check the " stop if true" button. (apply to entire column also)

Make that =$L1 instead

[63falcondude]

Hello and welcome to the forum.

The value in cell L11 is "" not 0.

"" is technically a text value and all text values are considered to have a greater value than any number.

Also, since you are using a table, you might as well make your workbook more efficient by only applying the CF to the table instead of the entire column.

Select cell L2 > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet (we're starting over)

Select L2:L16 > Conditional Formatting > New Rule > Use a formula
=AND(L2<>"",L2>0)
Format: Fill green > OK > OK

Select L2:L16 > Conditional Formatting > New Rule > Use a formula
=L2<0
Format: Fill red > OK > OK

[tool4daman]

Thanks for the quick response, Jeff.
I'm a novice on Excel- I think you mean to input your answers as a formula in each cell. Is that correct? I'm not really sure where I input the formulas you gave me to replace my highlight rules with.

[tool4daman]

Thank you all for your help! I was able to get it to work following your instructions. I really appreciate all of you!!!

[jeffreybrown]

Sorry about that, but glad you have your solution. I should have posted...

Conditional Formatting

• Highlight applicable range >> =$L$2:$L$16
• Home Tab >> Styles >> Conditional Formatting >> New Rule
• Select a Rule Type: Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND(LEN(L2)>0,L2>0) >> Green
• Format… [Number, Font, Border, Fill]
• OK >> OK

Conditional Formatting

• Highlight applicable range >> =$L$2:$L$16
• Home Tab >> Styles >> Conditional Formatting >> New Rule
• Select a Rule Type: Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND(LEN(L2)>0,L2<0) >> Red
• Format… [Number, Font, Border, Fill]
• OK >> OK

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[tool4daman]

In editing this spreadsheet, I've come across another question:

I will be adding rows to this table throughout the year. I'd like the formulas I have entered into columns I, K, and L to be copied automatically to each new row I add. I have these formulas entered properly into cells I17, K17, and L17. Is there a way to do that? Or will I have to manually copy the formulas into the new row each time I add the new row?

Thanks again for your time and help.

[Pepe Le Mokko]

Have a look at https://support.office.com/en-us/art...9-6C94334E492C

[tool4daman]

Thanks for the reference, Pepe. I looked there, but didn't find the answer. I know I can drag the cells from above down, and that will copy the formula.
But I'd like to know if there is a way for the table to do it automatically when I add another row.

Thanks.

[63falcondude]

You have to make the formula the same for the whole column, then when you add a new row, the table will expand and the formulas will automatically be dragged down to that next row.

[tool4daman]

Thank you, 63Falcon! That worked. I appreciate your help.