Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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:


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

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


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

share|improve this answer
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


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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .