Conditional Formating

avisoft20

Board Regular
Joined
Sep 10, 2016
Messages
59
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:)
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

avisoft20

Board Regular
Joined
Sep 10, 2016
Messages
59
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

avisoft20

Board Regular
Joined
Sep 10, 2016
Messages
59

ADVERTISEMENT

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
 

avisoft20

Board Regular
Joined
Sep 10, 2016
Messages
59
You're welcome. Thanks for the follow-up. :)
Thank you so much Peter,
if we want Sum of B2:f2 equal to or Less than A2 then it will be highlighted and if A2 is greater than B2:F2, it will not be highlighted.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,451
Messages
5,601,727
Members
414,470
Latest member
glukemey

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
Top