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.

 

[mike2008]

I don't have any experience in using them, but I think you need to use Scenarios with a solver to identify a valid combination. The help files might explain how to use this for your purposes.

 

[schielrn]

Welcome to the Board.

1) Will the random numbers in A1:A9 be unique?
2) Is there a maximum or minimum for the random numbers?
3) How does the value in A11 come about? Will you just be entering a value in A11 and seeing which numbers in A1:A9 can add up to that number?
4) Can there be more or less than 2 values adding up to sum to A11? i.e. A11 contains 10 --> A1 contains 10, would that be considered a match? A1 contains 2 A2 contains 3 A3 contains 5, would that be considered a match?

This will help us know exactly what you are needing. Thanks.

 

[grayma]

Thanks for your help. See the below answers to your questions.

1) Yes, the numbers in A1:A9 will be unique.
2) There is NO maximum or minimum for the random numbers
3) I will just be entering a value in A11 and seeing which numbers in A1:A9 can add up to that number
4) Yes, more or less than 2 values may add up to sum to A11

FYI, I'm using Excel 2003 in a Windows XP environment.

 

[schielrn]

I don't think this can be done with a native formula at least that I know of. Someone else may come along and prove me wrong. It could be done 1 of 2 ways. Both require VBA code, one being a user defined function(which I would not be able to create easily, but I believe you can use UDFs in conditional formatting?) and the other being a macro that you would run and then it would format the cells. Is VBA an option?

 

[grayma]

Yes, VBA is an option.

 

[schielrn]

I believe this should work for you:

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
        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

This is based on the fact that your random numbers are in A1:A9 and the number to sum to is in A11, like you stated. It can be modified to other ranges. Hopefully it works, I wa only able to test it a couple times. Just drop it into a standard module and run the macro on the sheet. Post back with any problems you incur.

 

[grayma]

Unfortunately, it did not work. The cells that it highlights yellow do not sum up to cell A11.

 

[grayma]

Upon further investigation, it looks like its just needs to be tweaked a bit. It highlight the right cells but also the cells inbetween. So, for example, A1 + A3 = A11. It will highlight A1, A2, and A3.
If A1 + A2 = A11, it will highlight the correct cells.

 

[schielrn]

Could you post a sample of the 9 unique numbers and the 1 you are summing to that you see it highlight A1 A2 and A3?