Highlight Sequential Cells That Sum Up To A Value

[Prabhu]

Hi Firend,

I need macro which has to highlight cells in the particular column up to the extent of the given value.

The excel sheet has around the 30-50 K rows many columns with value.

If I want to pick up the value cell which is equal to 10 laksh then it should highlight the cells which total comes to 10 lakhs.

In case sum of the cells not come exactly to 10 laksh then it should show very near to 10 lakhs.

I have attached the sample date for your reference.

Plz do the needful.

Prabhu.

[Excel Fox]

Why don't you try conditional formatting....

=SUM($K$2:$K2)<$L$1 dragged down till the last row

See attachment

[Prabhu]

Why don't you try conditional formatting....

=SUM($K$2:$K2)<$L$1 dragged down till the last row

See attachment


Hi Admin,

This formula is considering from first cell till near by value.If the value is exceed the value then it shows as false.

Attached workbooks shows by applying this formula we can get the value of Rs.929383 but if we find manually we can get exact value of 10 L(Highlighted in Green).

So we need to check entire cells in the particular column and find out very near by value.

Is there any way to do so(By formula or Macro)?

Plz help.

[snb]

Sub snb()
j = 1
Do Until Application.Sum(Cells(2, 11).Resize(j)) >= Cells(1, 12).Value
j = j + 1
Loop
Cells(2, 13).Resize(j - 1).Interior.ColorIndex = 4
End Sub

[Prabhu]

Sub snb()
j = 1
Do Until Application.Sum(Cells(2, 11).Resize(j)) >= Cells(1, 12).Value
j = j + 1
Loop
Cells(2, 13).Resize(j - 1).Interior.ColorIndex = 4
End Sub

Hi,

Same result am getting.

It is not hithlighting very near to 10 L.

Plz fidn the attached working which i have highlighted the cells(Manualy) which is very near to 10 L.

Kindly do the needful.

Prabhu

[Admin]

Hi,

Have a look at this: Find a set of amounts that match a target value (http://www.tushar-mehta.com/excel/templates/match_values/index.html#VBA_multiple_combinations)

[snb]

You must be kidding; it does exactly what you were asking for.

I fear you have no idea what the code is doing

You can easily adapt it to:


Sub snb()
j = 1
Do Until Application.Sum(Cells(2, 11).Resize(j)) >= Cells(1, 12).Value
j = j + 1
Loop
Cells(j, 11).Interior.ColorIndex = 4
End Sub

[Excel Fox]

Why don't you try =SUM($K$2:$K2)<=$L$1 instead?