• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formattin

niting

New Member
Chandoo,


Hie. I have benefited immensly from your blog and real heart felt thanks to you.

Have a issue with your favourite topic. There is data in column A and Column B. I want to set a rule that highlits the cell values in Col A and Col B, when zero, only and only when both columns contain zero.

Any suggestions?
 

VaraK

New Member
Hi niting,


Select your range. In conditional formatting, rule type: Use a formula to determine which cells to format.


formula is : =AND($A1=0,$B1=0)


Go ahead and format the cells as you like. Hope this helps.
 

niting

New Member
VaraK,


Thanks for the reply.


I am afraid it is not working. The formula in the conditional formatting for cells in the range is showing reference error and therefore the cells dont get higlited as per the condition.


Wld appreicate if you could offer alternate solution or point out where I mit be making an error.
 

Hui

Excel Ninja
Staff member
Niting

Select the area eg:A1:B30

Goto Conditional Formatting and Clear Rules from Selected Cells

Add a New Rule, Use a Formula =AND($A1=0,$B1=0) and select your required format

Apply


Errors on the worksheet will not effect Conditional Formats in other cells
 

niting

New Member
Hui,


Seems the cells are stubborn. Just not complying.


It does not work and when i randomly click on any cell in the range and go to CF, the formula shows as AND($a#Ref!=0,$b#Ref!=0)


I hope I have communicated the exact nature of problem now. Would appreciate if you can solve my problem.
 

Hui

Excel Ninja
Staff member
Is the Range part of a Table or Filtered in any way ?

What Formulas are in A1 and B1 or A2 and B2 ?

Have you applied any other CF's to the area ?


Go back to Conditional Formatting, Manage Rules and Edit the Rule

Make sure the Formula is Correct =AND($A1=0,$B1=0)

Apply
 

niting

New Member
Hui,


The entries in Col. A & B is through Vlookup and manual entry and the range does not form part of any table and neither any CF is applied to any cells in that range.


BTW, i am using Open office3.2. I hope the formulas apply equally to excel and Open office. The formula just does not seem to work in this range.


Thanks
 

TessaES

New Member
Make sure A1 is the active cell in your range when you apply the conditional formatting. I think the problem is that B30 was the active cell and then you should enter the formula as =AND($A30=0;$B30-0).

It does work in OpenOffice.
 

niting

New Member
Hey TeesaES,


Thanks a lot, it works!!!!


BTW, if and when u have time, could u explain to me d logic behind it not workin earlier with cell reference as "A1" and now working with reference "A30"???


Thanks again
 

Hui

Excel Ninja
Staff member
Niting

When you select a range, the range has an active cell.

When making conditional formatting you write an equation and it is applied to all cells in your range, with reference to the absoluteness or relativeness of the formula to your active cell.

Excel applies the same rules but maybe less strict in its application than Open Office.
 
Top