Conditional Formating

avisoft20

Board Regular
Joined
Sep 10, 2016
Messages
63
Dear All,
I need your support.
I want the conditional formatting formula. I am trying to that
Conditions:
Sum of B2:f2 equal to or greater than A2, will be highlighted, Do not want to add a column for the sum of B2:f2
ABCDF
TotalJan-20Feb-20March-20April-20
208755
155863
251011510

The result will be for :
TotalJan-20Feb-20Mar-20Apr-20
208755
155863
251011510

Thanks, And Regards
Avisoft20:)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Not sure why you only have two cells highlighted in the third example since the total of those two cells is 21 which is less than the 25 in column A?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This is my take on it.

20 12 26.xlsm
ABCDE
1TotalJan-20Feb-20Mar-20Apr-20
2208755
3155863
4251011510
52001011510
651011510
7351011510
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E7Expression=COLUMNS($B2:B2)<=MATCH(TRUE,SUBTOTAL(9,OFFSET($B2,,,,COLUMN($B2:$E2)-COLUMN($A2)))>=$A2,0)textNO
 
Upvote 0
Solution
Not sure why you only have two cells highlighted in the third example since the total of those two cells is 21 which is less than the 25 in column A?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This is my take on it.

20 12 26.xlsm
ABCDE
1TotalJan-20Feb-20Mar-20Apr-20
2208755
3155863
4251011510
52001011510
651011510
7351011510
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E7Expression=COLUMNS($B2:B2)<=MATCH(TRUE,SUBTOTAL(9,OFFSET($B2,,,,COLUMN($B2:$E2)-COLUMN($A2)))>=$A2,0)textNO
Dear Peter,
It's working,
Thank you for your valuable time given to me.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
Dear All,
I need your support.
I want the conditional formatting formula. I am trying to that
Conditions:
Sum of B2:f2 equal to or Less than A2, will be highlighted, Do not want to add a column for the sum of B2:f2
ABCDF
TotalJan-20Feb-20March-20April-20
208755
155863
251011510

The result will be for :
TotalJan-20Feb-20Mar-20Apr-20
208745
155823
251011310

Thanks, And Regards
Avisoft20
 
Upvote 0
if we want Sum of B2:f2 equal to or Less than A2 then it will be highlighted
Is this what you mean? If not some clear sample data and expected results please.

20 12 26.xlsm
ABCDE
1TotalJan-20Feb-20Mar-20Apr-20
2208755
3155863
4251011510
52001011510
651011510
73510111410
CF (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E7Expression=SUM($B2:B2)<=$A2textNO
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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