Excel Formula using Conditional Formatting

[grayma]

Given the below criteria, I am in need of a formula.

1. I have a range of random numbers within a column, i.e. A1:A9
2. A specific number will appear in a cell at the end of that column, i.e. A11
3. I need to see via conditional formatting which of the above cells add up to that number.

I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.

Any assistance would be greatly appreciated.

 

[grayma]

I'm having some difficulty producing that result again.

Now it is highlighting the number before the correct combination as opposed to inbetween.

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right width=64 height=17 x:num>1000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>19</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1500</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>20000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>20</TD></TR></TBODY></TABLE>

I've also seen it highlight >1 correct combination of numbers and include the cells in between. You'll see that A1 + A2 = 20 and A8 + A9 = 20, but it highlights everything in between also.

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right width=64 height=17 x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>19</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>1000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>2000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>3000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>4000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>5000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" align=right height=17 x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>20</TD></TR></TBODY></TABLE>

 

[schielrn]

It works with your solution and with some other random ones I tested, but I have noticed some little flaws that I don't have time right now to fix, but can look into it probably later this week. I need to sit down and scrap waht I have a logically think through all aspects. Here is what I have if you want to start trying anything on your own?

Sub colorsum()
Dim total As Double
Dim arr() As String
Dim targetRange As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("A1:A9").Interior.ColorIndex = -4142
targetRange = "A11"
For j = 1 To 9
    For h = j To 9
    If Cells(h, 1).Value > Range(targetRange) Then Exit For
        total = 0
        counter = 0
        ReDim arr(9)
        arr(counter) = j
        total = total + Cells(j, 1).Value
        For i = h To 9
            
            If i <> h And total + Cells(i, 1).Value <= Range(targetRange).Value Then
                total = total + Cells(i, 1).Value
                arr(counter + 1) = i
                counter = counter + 1
            ElseIf total > Range(targetRange).Value Then total = total - Cells(i, 1).Value
            End If
            
            If total = Range(targetRange).Value Then
                For k = 0 To counter
                    Cells(arr(k), 1).Interior.ColorIndex = 6
                Next k
            End If
        Next i
    Next h
Next j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[schielrn]

I see one flaw that I need to look into how to handle:

If you have the following

1
2
3
4
5
6
7
8
21

20

1, 2, 3, 6 and 8 should add up to 20, but the code how I have it does not test all combinations. It will add 1 + 2 + 3 + 4 + 5 to give 15 and then it will check the rest after 5 to see if it equals 20 if that is not the case it will move onto 1 + 3 + 4 + 5 + 6 to give 19 and then 20 still cannot be reached then it would move onto 1 + 4 + 5 + 6 and so on. It would need to somehow get to 1, 2, 3, 6 and 8, but like I said I need to sit down and just map out exactly how this code needs to behave to be able to make sure I am checking all combinations. So I need to figure out a way for it to check all those combinations.

 

[grayma]

I appreciate any help you can give. I'm out of my realm.

If its easier to only find one combination of numbers, then that's fine. Perhaps we could run the macro several times on a range to see the different combinations of numbers each time....

Thanks again.

 

[mike2008]

Have a look at "Puzzle solving" posted by melvinm, this looks at a list of numbers, and uses all simple mathematical signs (), +, -, /, x to get a solution from a list of possible numbers.

It might be possible to adapt that solution to solve this problem by restricting the signs used.

I don't see how you will fully solve your multiple solutions with the highlighting method, so it might be more appropriate to generate all the possible solutions as a discrete list in the example.

 

[grayma]

Thanks much! It looks like that code would meet my needs.

 

[Greg Truby]

Boy, it's been such a long time. But it seems to me that this (list all combinations of a set of numbers that add up to a given sum) is a "classic" logic problem. I just have no clue what is the standard name of this particular logic problem. But I believe I saw a thread on this several years ago. If you know the common name for this particular logic challenge, I suggest searching here to see if that name was used in the old thread(s).

 

[Oaktree]

Greg:

This thread http://www.mrexcel.com/forum/showthread.php?t=16533 is probably what you were thinking of...

 

[Greg Truby]

Greg:
This thread http://www.mrexcel.com/forum/showthread.php?t=16533 is probably what you were thinking of...

Bingo! That is exactly the thread I was thinking of! I had noticed the thread had been resurrected in the lounge but I had not perused it to refresh my memory. Did you remember it from back in 2002? Or was it only due to its resurfacing in the lounge that you recalled it?

 

[Oaktree]

I remembered it was a previous challenge of the month, but only from its resurfacing did I know which one...