Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a bit of a problem evaluating an excel cell. Example:

          M   M   M   M   M   M   M   M   M   
          1   2   3   4   5   6   7   8   9
2;5;7
1;9
3;5;7;9   

I have a number of excel cells which contain numbers (months). In the first column I have a series of numbers. I want to use conditional formatting to color the corresponding cells in the right columns. If correctly colored I would get something like this:

          M   M   M   M   M   M   M   M   M   
          1   2   3   4   5   6   7   8   9
2;5;7         X           X       X
1;9       X                               X
3;5;7;9           X       X       X       X

The formula I have now is this:

IF(ISNUMBER(FIND(L$22;$K23));$H23;"")

but the problem is that cells are colored too which contain part of a number. If I enter 10;15 as input I get this:

          M   M   M   M   M   M   M   M   M   M   M   M   M   M   M
          1   2   3   4   5   6   7   8   9  10  11  12  13  14  15
10;15     X               X                   X                   X

because 1 and 5 are found too. I only want column 10 and 15 to be marked. How can I change the formula or the input?

share|improve this question

2 Answers 2

Tampering with your input data may be a 'cheat' - but perhaps easiest! I'd add a semi colon at the start and end of your input data and then use

=FIND(";"&L$22&";",$K23)>0

though I think you would have to replace the comma in this formula with a semi colon also.

share|improve this answer
1  
No need to change the source data. Use =FIND(";"&L$22&";",";"&$K23&";")>0 (or in the local of the OP =FIND(";"&L$22&";";";"&$K23&";")>0) –  chris neilsen Dec 5 '12 at 7:02
    
@chrisneilsen That’s a much better answer (ie adding the ‘extra’ delimiters ‘on the fly’). I’m going looking at some of your other answers for gems like that! –  pnuts Dec 5 '12 at 8:42
    
Thanks for comments. –  Stephen Younger Dec 5 '12 at 10:25

I found some VBA-function which I changed to this and it works:

Function CHECK_INTERVALL(str, colmonth, sepChar)

Dim months As Variant
months = Split(str, sepChar)
Dim i As Integer

CHECK_INTERVALL = False

For i = LBound(months) To UBound(months)
        If CInt(colmonth) = CInt(months(i)) Then
            CHECK_INTERVALL = True
        End If
Next i

End Function

If the current month is in the list I get true and the conditional formatting colors the cell.

share|improve this answer
    
Do you realise you may, indeed are encouraged, to switch hats (as this is the hat season!) and in your capacity as OP accept this (your own) answer? There should be an outlined tick visible to you that you can click on, to turn green. –  pnuts Dec 22 '12 at 1:18

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.