# Conditional Formats for Odd and Even Columns

by Allen Wyatt
(last updated June 21, 2018)

Zar has a need to apply a conditional format to the values in column A of a worksheet, but he can't figure out what the rules might be. Besides column A, he also has data starting in column B and he periodically adds new columns of data. If there is data in all the odd columns starting with B, Zar wants one format applied in column A. (B is data column 1 for his worksheet, so he considers it odd.) If there is data in all the even columns beginning with C, Zar wants a different format applied in column A. If there is data in all the data columns starting with B—however many that might be—then he wants a third format applied.

As Zar has no doubt figured out, you can easily create a formula to determine whether there is information in columns B and C and apply formatting accordingly. In fact, a simple formula such as these will do the trick:

```=COUNTA(B1:C1)=2
=COUNTA(B1)=1
=COUNTA(C1)=1
```

The first formula returns True if there is information in both B and C, the second if there is information in B, and the third if there is information in C. As long as you select "Stop If True" for each rule/formula, then your formatting will work fine.

Creating a formula for multiple columns beyond B and C is only marginally more difficult. The same three types of formulas, in order, would be as follows:

```=COUNTA(B1:G1)=6)
=COUNTA(B1,D1,F1)=3
=COUNTA(C1,E1,G1)=3
```

You could easily add additional cell references to the formulas, as needed. Such an approach returns True in only three conditions: if ALL cells in the range B1:G1 have something in them, if ALL odd cells (B1, D1, F1) have something in them, and if ALL even cells (C1, E1, G1) have something in them. It won't return True if only some of the cells in the range have values in them. For instance, there are values in cells B1, C1, and E1, then it won't return True and none of the criteria for formatting will be met.

While these all work fine with the noted limitation, they aren't exactly what Zar is looking for—he wants a formula that will detect how many columns are being used week after week, as he continues to add data to columns, and adjust the formula accordingly without the need to manually edit the formula to take into account the added data. In other words, if he adds data to column H, he would want the formulas to automatically be adjusted to take into account the added column:

```=COUNTA(B1:H1)=7)
=COUNTA(B1,D1,F1,H1)=4
=COUNTA(C1,E1,G1)=3
```

That is obviously a more complex need. Perhaps the best way to approach the problem is to create a user-defined function (a macro) that can look at a range of cells and determine if one of the three criteria are met. Consider the following macro:

```Function CellChk(crng As Range) As String
Dim iNumOdds As Integer
Dim iNumEvens As Integer
Dim iOdds As Integer
Dim iEvens As Integer
Dim iTots As Integer
Dim iTotCells As Integer
Dim rWork As Range
Dim rCell As Range
Dim iLastCol As Integer
Dim sTemp As String

iOdds = 0
iEvens = 0
iTots = 0

' Figure out the real last column in the worksheet and set range
iLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
LookIn:=xlFormulas).Column
Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))

iTotCells = rWork.Count
iNumOdds = (iTotCells + 1) \ 2    ' Number of odd columns
iNumEvens = iTotCells - iNumOdds  ' Number of even columns

For Each rCell In rWork
If rCell <> "" Then
If ((rCell.Column - 1) Mod 2) = 1 Then
iOdds = iOdds + 1
Else
iEvens = iEvens + 1
End If
iTots = iTots + 1
End If
Next rCell

sTemp = ""
If iTots = iTotCells Then
sTemp = "t"
ElseIf iOdds = iNumOdds Then
sTemp = "o"
ElseIf iEvens = iNumEvens Then
sTemp = "e"
End If
CellChk = sTemp
End Function
```

You use the macro by passing it an address in the row you want to check. So, for instance, if you were applying the conditional formatting rule to cell A3, you would pass the macro an address of B3 or C3—anything except A3, as that will cause a circular reference. The macro looks for the last cell used in that row and then determines how many odd and even cells have something in them. The macro returns any of four values; if the first criteria is met (all cells in the row starting with column B have something in them) then a "t" is returned. If all the odd columns (with B being the first odd column) have something in them, then "o" is returned. If all the even columns (with C being the first even column) have something in them, then "e" is returned. If none of the three criteria are met, then the function returns nothing.

You'll still need to set up three conditional formatting rules that rely on the evaluation of a formula. Here are three you can use with this macro:

```=CellChk(B1)="t")
=CellChk(B1)="o")
=CellChk(B1)="e")
```

These examples are for applying a conditional format to cell A1; adjust the cell references to the correct row that you want the macro to analyze. Remember that even though you specify a single cell (B1 in these examples), the macro calculates how many cells in the row to actually look at.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5945) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Data Source File in Word 2003

The security features introduced in Word 2003 resulted in a change in the dialog boxes you see when opening mail-merge ...

Discover More

Condensing Multiple Worksheets Into One

Want a quick way to combine your worksheets? This tip features a simple macro to do the task for you.

Discover More

Passing a Data File Name via Command Line to a Macro

Using the command line to pass paramaters to a program is a common occurrence. Using the command line to pass parameters ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

##### More ExcelTips (ribbon)

Noting Inactivity within a Timeframe

There are many times when you are creating a worksheet that you need to analyze dates within that worksheet. Once such ...

Discover More

If you need to shade alternating rows in a data table, you'll want to examine how you can accomplish the task with ...

Discover More

Changing Font Face and Size Conditionally

Conditional formatting does not allow you to change the typeface and font size used in a cell. You can write your own ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one less than 6?

2016-11-02 22:01:24

John

Thank you Bryan, not only for the elegant solution but also for the excellent and through explanation.

2016-11-02 17:41:14

Robin

That's delightfully elegant Bryan - a veritable greyhound of a solution

2013-10-07 08:14:57

Bryan

The macro is ok, but this can be done entirely through worksheet formulas. I've used named ranges simply to make them easier to use, but you could instead type the whole formula into the conditional formatting dialogue.

First I created the following named formulas, which are used to set up the conditional formatting formulas:

NumHeaders simply gives a count of everything in Row 1 and subtracts 1. This of course assumes that every column has a header (as it should) and nothing else is put in this column. NumEven simply counts the number of even columns, assuming B is the first odd column, and NumOdd is the number of odd columns. I used a trick on this one that TRUE when used in addition equals 1, so if there are an odd number of columns, there will always be one more than the number of even columns.

ThisRow is a reference to all the data in a given row (excluding Column A). Note that I selected a cell in Row 1 when creating the formula; as you use the formula on different rows, the row numbers will change, thus referencing those rows. The formula looks complicated, but it's actually just a range. I'm taking advantage of the fact that INDEX actually returns a reference, not a value. Since it returns a reference, you can use it anywhere you would otherwise use a cell reference. Many people use OFFSET formulas to do the same thing, but I prefer not to use a volatile formula if I can help it. The INDEX trick is better.

Next, I set up three named formulas for each of the conditions:

AllEven = SUMPRODUCT(ISODD(COLUMN(ThisRow))*(ThisRow<>""))=NumEven
AllOdd = SUMPRODUCT(ISEVEN(COLUMN(ThisRow))*(ThisRow<>""))=NumOdd

The AllFilled formula is fairly straight forward: if the number of data points equals the number of headers, then every column has a value. AllEven counts the number of non-blank cells where the column is odd (note that our definition of "odd" is different than Excel's definition, so we have to flip the functions), and compares that to the number of even columns. AllOdd, obviously, just takes the opposite approach.

AllFilled, AllOdd, and AllEven take the place of the last three formulas in the article (which, by the way, have an extra parenthesis).

Note that if for some reason you are uncomfortable with named ranges, you could write them out as a single formula by substituting the formulas back into the named ranges. For example, AllFilled would be:

=COUNTA(\$B5:INDEX(\$B5:\$XFD5,COUNTA(\$1:\$1)-1))=COUNTA(\$1:\$1)-1)

And AllOdd would be:

=SUMPRODUCT(ISEVEN(COLUMN(\$B1:INDEX(\$B1:\$XFD1,COUNTA(\$1:\$1) - 1)))*(\$B1:INDEX(\$B1:\$XFD1,COUNTA(\$1:\$1) - 1)
<>""))=INT((COUNTA(\$1:\$1) - 1)/2) + ISODD(COUNTA(\$1:\$1) - 1)

I'll let you figure out AllEven :)

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.