Conditional formatting

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,189
Office Version
  1. 2019
Platform
  1. Windows
Hello guys
I have entered 2 entries in the excel sheet. In the second row the sum of the amounts is zero and hence matched. In the third row the sum of the amounts hasn’t matched. What I am trying to do is I want to use conditional formatting. If the total of any row is not matched, then I want the amount cells of that row to display a color. If I change and correct the amount, then it should remove the color. What formula should I apply in the new rule…?
Query conditional formatting.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
1ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD Help Total
2Bank-30010January100February200March300April400May500June600July700August800September900October1000November1100December1200January1300February1400March1500April1600May1700June1800July1900August2000September2100October2200November2300December2400Round Off10Matched0
3Bank-30010January100February200March300April400May500June600July700August800September900October1000November1100December1200January1300February1400March1500April1600May1700June1800July1900August2000September2100October2200November2300December2400Round Off-10Not Matching-20
PostData
Cell Formulas
RangeFormula
BE2BE2=IF(BF2=0,"Matched","DebitCreditNotMatching")
BF2:BF3BF2=F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2
BE3BE3=IF(BF3=0,"Matched","Not Matching")
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,189
Office Version
  1. 2019
Platform
  1. Windows
Sorry The formula in BE2 was not changed. It is =IF(BF2=0,"Matched","Not Matching"). The cells which should color if not matched are F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,695
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
1ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD Help Total
2Bank-30010January100February200March300April400May500June600July700August800September900October1000November1100December1200January1300February1400March1500April1600May1700June1800July1900August2000September2100October2200November2300December2400Round Off10Matched0
3Bank-30010January100February200March300April400May500June600July700August800September900October1000November1100December1200January1300February1400March1500April1600May1700June1800July1900August2000September2100October2200November2300December2400Round Off-10Not Matching-20
Data
Cell Formulas
RangeFormula
BE2BE2=IF(BF2=0,"Matched","DebitCreditNotMatching")
BF2:BF3BF2=F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2
BE3BE3=IF(BF3=0,"Matched","Not Matching")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:BB3Expression=AND($BF2<>0,ISEVEN(COLUMN(E2)))textNO
 
Solution

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
2,189
Office Version
  1. 2019
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBF
1ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD Help Total
2Bank-30010January100February200March300April400May500June600July700August800September900October1000November1100December1200January1300February1400March1500April1600May1700June1800July1900August2000September2100October2200November2300December2400Round Off10Matched0
3Bank-30010January100February200March300April400May500June600July700August800September900October1000November1100December1200January1300February1400March1500April1600May1700June1800July1900August2000September2100October2200November2300December2400Round Off-10Not Matching-20
Data
Cell Formulas
RangeFormula
BE2BE2=IF(BF2=0,"Matched","DebitCreditNotMatching")
BF2:BF3BF2=F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2
BE3BE3=IF(BF3=0,"Matched","Not Matching")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:BB3Expression=AND($BF2<>0,ISEVEN(COLUMN(E2)))textNO
Very nice Fluff. Perfect as usual. Thanks,
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,695
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,176,036
Messages
5,901,048
Members
434,866
Latest member
nachoabasile

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