SOLVED

Excel formula for bank rec

%3CLINGO-SUB%20id%3D%22lingo-sub-2975383%22%20slang%3D%22en-US%22%3EExcel%20formula%20for%20bank%20rec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2975383%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20match%202%20columns%20against%202%20columns.%20I%20want%20the%20columns%20to%20turn%20red%20if%20they%20dont%20match%20and%20green%20if%20they%20do.%20As%20long%20as%20the%20dates%20have%20the%20same%20sum%20on%20money%20I%20want%20them%20to%20both%20turn%20green.%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3EAmount%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3EAmount%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F01%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%244.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F01%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%247.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F01%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%243.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F02%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%242.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F02%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%242.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F03%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%241.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F03%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%241.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F04%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%243.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2212.5%25%22%3E10%2F04%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%241.00%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2212.5%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2975383%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2975469%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20for%20bank%20rec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2975469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1221555%22%20target%3D%22_blank%22%3E%40aviess94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%20Click%20in%20A2%20or%20D2%20and%20select%20Conditional%20Formatting%20%26gt%3B%20Manage%20Rules...%20to%20see%20the%20definition%20of%20the%20rules%20that%20I%20created.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980122%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20for%20bank%20rec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980122%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20this%20is%20a%20huge%20help%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2981739%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20for%20bank%20rec%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2981739%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3EPlease%20see%20attached.%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20match%20the%20amount%20for%20the%20correct%20dates.%20Even%20if%20the%20dates%20ate%20not%20parallel%20to%20each%20other.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20also%20trying%20to%20make%203%20of%20the%20same%20formals%20on%20the%20spreadsheet.%20Not%20sure%20how%20to%20copy%20that.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EPlease%20advise.%3CBR%20%2F%3EThank%20you.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi ,

 

I am trying to match 2 columns against 2 columns. I want the columns to turn red if they dont match and green if they do. As long as the dates have the same sum on money I want them to both turn green.

DateAmount DateAmount   
10/01$4.00 10/01$7.00   
10/01$3.00 10/02$2.00   
10/02$2.00 10/03$1.00   
10/03$1.00 10/04$3.00   
10/04$1.00      
7 Replies
best response confirmed by aviess94 (Occasional Contributor)
Solution

@aviess94 

See the attached sample workbook. Click in A2 or D2 and select Conditional Formatting > Manage Rules... to see the definition of the rules that I created.

Thank you so much this is a huge help

@Hans Vogelaar 

Thank you.

Please see attached.

I am trying to match the amount for the correct dates. Even if the dates ate not parallel to each other.

 

I am also trying to make 3 of the same formals on the spreadsheet. Not sure how to copy that.


Please advise.
Thank you.

@Hans Vogelaar 

@aviess94 

I don't know what exactly you did. but the rules for columns F to H were completely messed up. I removed them and created two new ones.

Great. Thank you so much.

How can I duplicate these rules to be applied to a different range of cells?

@aviess94 

Select a cell with conditional formatting.

On the Home tab of the ribbon, click Conditional Formatting > Manage Rules...

Select one of the rules and click Edit Rule...

Press Tab once to select the formula box.

Press Ctrl+C to copy the formula.

For ease of use, I'd either paste the formula into an empty cell, or into for example Notepad.

Inspect the formula carefully, then modify it for use in another range.

Thank you God bless you