Conditional Formatting and Noncontiguous Ranges

I started fiddling around with a Sudoku spreadsheet this week. I got a Sudoku book in my Christmas stocking. I didn’t have terrible difficulty solving most of the puzzles, but it’s obvious after the first one that Excel could be a nice tool for keeping track of which numbers can and cannot go in certain spaces.

First I downloaded Andy Pope’s Sudoku workbook and the one on Office Online. The former is a masterful piece of coding, but if you’ve ever seen Andy’s work before, you probably won’t be surprised. I plugged in the toughest puzzle from the book, even unintentionally omitting one number, and it solved it in about three milliseconds. The latter workbook was equally intriguing because it used circular formulas and no VBA. It could not, however, solve the super tough puzzle from the book.

I’ll discuss this workbook more when I post it, but on to my immediate problem. I have a range name that contains noncontiguous cells called Grid1. It refers to


and I want to use it in a conditional formatting formula. Apparently COUNTIF doesn’t cotton to noncontiguous ranges. Juan Pablo Gonzalez says it can be done but his example uses multiple contiguous ranges, whereas I have just one range. INDIRECT apparently doesn’t like noncontiguouosity either because I couldn’t even get =SUM(INDIRECT("grid1")) to work, although =SUM(Grid1) works fine.

The formula I’m trying to enter in conditional formatting is =OR(ISNUMBER($B$2),COUNTIF(Grid1,B1)>0,COUNTIF(Row1,B1)>0,COUNTIF(Col1,B1)>0). Basically, it means that if there’s a number in B2, a 1 in Grid1, Row1, or Col1, then format B1 (which contains a 1). I’m not interested in creating 81 (9×9) unique conditional formatting formulas, but if I can’t solve this I’ll probably write a macro to create the formulas. That should only take about twice as long as doing it manually, assuming I could do it without errors. Any ideas?

Posted in Uncategorized

9 thoughts on “Conditional Formatting and Noncontiguous Ranges

  1. You could write a user-defined function for just the counting of the non-contiguous range of cells that match the criteria:

    Function CountifNC(rInputRange As Range, sCriteria As Single) As Single

    Dim i As Integer
    Dim c As Range

    i = 0

    For Each c In rInputRange.Cells
    If c.Value = sCriteria Then i = i + 1

    CountifNC = i

    End Function

    The formula for the conditional formatting of course becomes:


    I could only get it to work if the user-defined function was the last argument passed in the OR function.

  2. When you get that solved, try the 15×15 SuDoku puzzles?

    I too have been recently bit by the SuDoku bug and solved most of the puzzle reasonably rapidly (about 8 to 10 minutes each) in a little stocking stuffer. So, I went to the internet to look for a ‘free’ generator for puzzles and fount those 15×15. I literally went crosseyed – Yikes!

  3. Wow. That is an interesting one.

    I haven’t had much luck so far, but have found that you can use =INDEX(grid1,,,1) to iterate through the cells in the range (e.g. =INDEX(grid1,,,2), etc.). However, I haven’t been able to combine it with anything useful so far.

  4. Dick,
    I don’t have a solution to your contiguosity problem, but I have two ideas:
    1. Would some of the fancy SUMPRODUCT with double minus signs work on non-contiguous ranges?
    2. How about building a named formula with relative references as the input formula in the conditional format equation? (Haven’t tried this myself).

    HNY to all the DDE blog folks!

  5. Dick,

    I don’t know if this will help (I have not messed with Soduku yet), but to mimic countif(Grid1,B1), I used


    since your Grid1 cells are evenly spaced along the rows and columns. EntireGrid is a defined name range =Sheet1!$B$2:$L$10 encompassing all the Grid1 cells.

  6. Dick,

    I noticed an error in my function, the formula should be:


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.