# Adding integer values within a single cell, then assigning a color based on its even or oddness.

#### matwil

##### New Member
I'm very new to VBA but I'm trying to take a spreadsheet of integer values, and indicate whether the sum of each cells' individual digits is even or odd, then color them accordingly. I chatted with a more experienced coder last night but we couldn't tie a knot in it (he was admittedly drunk, and did not have Excel installed on his machine). Anyways I started with a copied code from an office support web page:

 1 2 3 4 5 6 7 8​ Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double Dim arr As Variant Dim xIndex As Long arr = Split(pWorkRng, xDelim) For xIndex = LBound(arr) To UBound(arr) Step 1 SumNums = SumNums + VBA.Val(arr(xIndex)) Next End Function

<tbody>
</tbody>
This has two problems in my case:
• 1: It only adds for cells with letters and numbers, not just numbers.
• 2: This was made to express the sum in a new cell, and I'm not quite sure how to re-structure it to fulfill the purpose stated above.

Said page can be found here, if interested:

This did not keep me or my inebriated friend from trying, but the best I could come up with was:

Code:
``````Function SumNums(pWorkRng As Range, Optional xDelim As Integer) As Double
Dim arr As Variant
Dim xIndex As Long
arr = Split(pWorkRng, xDelim)
For xIndex = LBound(arr) To UBound(arr) Step 1
SumNums = SumNums + VBA.Val(arr(xIndex))
Next
If (SumNums Mod 2 = 0) Then
ActiveCell.Interior.Color = vbYellow
End Function``````

Apart from the added code past 'Next' to affect the cell color, the only difference this has to the above code is "As String" is now "As Integer" in an effort to make the code work for pure numbered cell values, the rest was too far over my head to even try changing. Any input or assistance is greatly appreciated, cheers!

This is on Excel 2013.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Jerry Sullivan

##### MrExcel MVP
Welcome to MrExcel,

One approach would be to combine a User Defined Function (UDF) with Conditional Formatting (CF). The example you found is a UDF. It is designed to work on a referenced cell because the UDF formula couldn't be entered in the same cell as the cell it is trying to evaluate (sum digits).

You can place a UDF in a CF rule, which allows a cell to be formatted based on whether the UDF evaluates that cell to be even or odd.

To try this, paste this code into a Standard Code Module of your workbook (like Module1).
Code:
``````Public Function HasOddSum(rCell As Range) As Boolean
'--adds the sum of all digits in a cell
'    then returns true if the sum is Odd, else False
'    code assumes there are no non-digit characters in rCell.Text

Dim lSumofDigits As Long, lCharNbr As Long
Dim sText As String

sText = rCell.Text

For lCharNbr = 1 To Len(sText)
lSumofDigits = lSumofDigits + Mid(sText, lCharNbr, 1)
Next

HasOddSum = lSumofDigits Mod 2
End Function``````

Now create a CF Rule:
Select a range of cells that has A1 in the upper left corner. (let's say A1:B10)

From the Ribbon: Home Tab > Styles > Conditional Formatting > New Rule...

In the CF Dialog: Use formula to determine what cells to format >
Format cells where this formula is True: =HasOddSum(A1)
Click Format button and color to be applied to cells that have Odd sums of digits

Test by entering values in Cell A1.
12 should get a color applied (odd sum)
11 should not a color applied (even sum)

Last edited:

Replies
14
Views
1K
Replies
1
Views
1K
Replies
3
Views
681
Replies
3
Views
620
Replies
0
Views
709

1,191,120
Messages
5,984,762
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back