Written by Allen Wyatt (last updated November 30, 2023)**This tip applies to** Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

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, 2016, 2019, and Excel in Microsoft 365.

**Comprehensive VBA Guide** Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out *Mastering VBA for Office 2010* today!

Conditional formatting is a powerful tool you can use to dynamically adjust the formatting of your worksheet. This tip ...

Discover MoreConditional formatting is a great tool. You may need to use this tool to tell the difference between cells that are empty ...

Discover MoreConditional formatting rules can be used to adjust the way in which information is displayed in Excel, such as the text ...

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

2019-01-26 23:49:01

Elliot W Penna

No error message for unmatched parentheses? =COUNTA(B1:G1)=6) ?

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.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments