Conditional Formattin

[niting]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.