current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I am working on a MS Excel 2010 spreadsheet where the users want to have some combined conditional formatting. Individually the functions I want to apply are working correctly, i.e this condition formula works:

= INDIRECT( ADDRESS( IF( ISEVEN( ROW() ), ROW() - 1, ROW() ), 22) ) = "6 Monthly"

As does this:

= OR( COLUMN() = 25, COLUMN() = 28)

But combining the formulae fails to apply any format. No error is returned, but the highlighted area is unaffected on applying the condition:

AND( INDIRECT(ADDRESS(IF(ISEVEN(ROW()), ROW() - 1, ROW()), 22)) = "3 Monthly", OR( COLUMN() = 25, COLUMN() = 28) )

If I want the cells where both conditions are met to be highlighted, what should I enter as the formula?

share|improve this question
    
What ranges are you applying each of the 'valid' rules to (incidentally the OR one is not valid) and what do you wish highlighted? –  pnuts Jan 16 at 1:43
    
I am applying this rule to the range $W$11:$AB$48, where every other cell in column 22 (V) has a string like "3 Monthly". The plan is that each cell where the corresponding cell of column 22 is "3 Monthly" AND the subject cell is in either column 25 (Y) or 28 (AB) should be highlighted. Can you tell me why the OR statement is not valid? It appeared to work, in that all cells in those columns were highlighted when I applied only that condition. –  user2727391 Jan 16 at 2:09
    
For the moment just re the OR statement. that's because the parentheses are not matched. –  pnuts Jan 16 at 2:24
1  
Couldn't see the forest for the trees... :) –  user2727391 Jan 16 at 2:29
    
Not behaving the way I expected (yet?) but may be a start:- in V11, V13 and V18: 6 monthly; the rule =OR($V11="6 monthly",COLUMN()=25,COLUMN()=28); the Applies to: =$W$11:$AB$18,$Y$11:$Y$18,$AB$11:$AB$18. BTW I'm using Excel 2007. –  pnuts Jan 16 at 3:06

3 Answers 3

up vote 0 down vote accepted

This works for me:

=(INDIRECT(ADDRESS(IF(ISEVEN(ROW()),ROW()-1,ROW()),22))="3 Monthly")*(OR(COLUMN()=25, COLUMN()=28))
share|improve this answer
    
In which version of Excel? –  pnuts Jan 16 at 10:36
    
@pnuts, I have excel 2013, but this formula doesen't work for me as well AND( INDIRECT(ADDRESS(IF(ISEVEN(ROW()), ROW() - 1, ROW()), 22)) = "3 Monthly", OR( COLUMN() = 25, COLUMN() = 28) ), but = INDIRECT( ADDRESS( IF( ISEVEN( ROW() ), ROW() - 1, ROW() ), 22) ) = "6 Monthly" and = OR( COLUMN() = 25, COLUMN() = 28) work fine –  simoco Jan 16 at 10:37
    
Getting interesting. For me, applying your rule to $W$11:$AB$48 does not highlight anything other than some/all (depending on V contents) of Columns Y and AB. –  pnuts Jan 16 at 10:53
    
@pnuts, does the formula And(,) works for you? I've no idea why it doesn't behave properly for me.. –  simoco Jan 16 at 10:57
    
No - AND(,) is same for me as for OP (no formatting and no error message). –  pnuts Jan 16 at 11:04
up vote 1 down vote

Your conditional formatting formulas seem overly complex to me

Select Y11:Y48 and then hold down CTRL key and also select AB11:AB48 and apply the conditional formatting formula

=$V11="3 Monthly"

Format as required

That will highlight the column Y and AB cells in every row where col V = "3 Monthly" - isn't that the requirement?

share|improve this answer
    
Based on accepted A (and NOT on Q) I have to agree with you. +1. –  pnuts Jan 17 at 1:03
    
Yep I might just go for your simpler option, though simoco's solution works and I still don't see why the admittedly over complicated formula didn't work. –  user2727391 Jan 17 at 2:59
    
I agree - it's difficult to seem why your version didn't work - you used =AND(condition1,condition2) whereas simoco's solution is =(condition1)*(condition2) - both of those would normally work but not here - I don't know the exact reason but probably because ROW() function returns an array like {25} rather than 25 - in some cases that isn't a problem but elsewhere it is. I imagine the multiplication using * forces an array to a number but AND doesn't do the same –  barry houdini Jan 17 at 12:13
up vote 0 down vote

Use IF:

=IF(INDIRECT(ADDRESS(IF(ISEVEN(ROW()),ROW()-1, ROW()),22))="3 Monthly",OR(COLUMN()=25,COLUMN()=28),0)

Hope this helps.

share|improve this answer
    
No luck I'm afraid. Still no effect. Also I'm not sure if IF is the right tool because I'm trying to highlight where both conditions are true simultaneously, not separately i.e the Intersection of the conditions rather than the Union. –  user2727391 Jan 16 at 1:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.