finding value of cell from sum cells gives same value

[mgstar]

hello guys,
am trying to make an excel sheet in my work , and i have got a serious problem.

the problem is:let us assume that i have in cell (A1)450 and in cell (b1)200
(b2)250
(b3)300
(b4)150

what i want to do is to highlight any cells if i sum 2 or more of them can be equals to my A1 cell in this case if i sum B1 and B2 it will be = to A1
same goes B3 and B4


i dont know if this can be done in excel or not,but any idea is appreciate.

[mrice]

You could try this macro

Option Explicit

Sub Test()
Dim MyRange As Range
Dim N As Integer
Dim NBinary As String
Dim RangeComb As Long
Dim X As Long
Dim Total As Long

Set MyRange = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
MyRange.Interior.ColorIndex = xlNone
RangeComb = 2 ^ MyRange.Count

For N = 1 To RangeComb - 1
    NBinary = Left("0000000000000000", Len(RangeComb) - Len(DecToBin(Str(N)))) & DecToBin(Str(N))
    Total = 0
    For X = 1 To MyRange.Count
        If Mid(NBinary, X, 1) = "1" Then
            Total = Total + MyRange(X)
        End If
    Next X
    
    If Total = Range("A1") Then
        For X = 1 To MyRange.Count
            If Mid(NBinary, X, 1) = "1" Then
                MyRange(X).Interior.ColorIndex = 4
            Else
                MyRange(X).Interior.ColorIndex = xlNone
            End If
        Next X
        Exit Sub
    End If
    
Next N

End Sub


Function DecToBin(ByRef D As String) As String
    Dim N As Long
    Dim Res As String
    For N = 31 To 1 Step -1
        Res = Res & IIf(CLng(D) And 2 ^ (N - 1), "1", "0")
    Next N
    N = InStr(1, Res, "1")
    DecToBin = Mid(Res, IIf(N > 0, N, Len(Res)))
End Function

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[mgstar]

master mrice thank you very much you

[Pepe Le Mokko]

Don't think you'll get much luck with that new title...

[FDibbins]

mgstar, hiow is that any more descriptive than your 1st title?

[mgstar]

FDibbins, I think you may suggest a title for me