VB coding
 

i am trying to do a bank rec where i match on:
check number (exact match)
deposit amount and date (both field need to match)
Column A = check #
column B = date
column C= amount
I may need to tell the macro if
check (c) or deposit (d)
if (c) match on column A
if (d) match on column B and C

i need to highlight the cells that match in both sheets.

thanks,

VB coding
 

You don't have to use a macro. Using conditional formating could give you
the same results

With the check you are looking for in Row 1 and the list of checks in Row 3
to 100

Add the formula in cell A3 inusing Format - Conditional formating and then
change Value IS to Formula IS

=And($A$1=$A3,$B$1=$B3,$C$1=$C3)

the in conditional formating choose the Pattern Tab and select a color.

Then copy cell A3 and select the rest of the table A3:C100 and use
PasteSpecial and coose Format. When you change cells A1:A3 the rows that
match will automatically hightlight.
"ricky" wrote:

i am trying to do a bank rec where i match on:
check number (exact match)
deposit amount and date (both field need to match)
Column A = check #
column B = date
column C= amount
I may need to tell the macro if
check (c) or deposit (d)
if (c) match on column A
if (d) match on column B and C

i need to highlight the cells that match in both sheets.

thanks,

VB coding
 

i am comparing two sheets.
checks = (sheet1.column A) looking for a match in (sheet2.column A)
Deposits = (sheet1.column B and C) looking for a match in (sheet2.column B
and C)

thanks,
"Joel" wrote:

You don't have to use a macro. Using conditional formating could give you
the same results

With the check you are looking for in Row 1 and the list of checks in Row 3
to 100

Add the formula in cell A3 inusing Format - Conditional formating and then
change Value IS to Formula IS

=And($A$1=$A3,$B$1=$B3,$C$1=$C3)

the in conditional formating choose the Pattern Tab and select a color.

Then copy cell A3 and select the rest of the table A3:C100 and use
PasteSpecial and coose Format. When you change cells A1:A3 the rows that
match will automatically hightlight.
"ricky" wrote:

i am trying to do a bank rec where i match on:
check number (exact match)
deposit amount and date (both field need to match)
Column A = check #
column B = date
column C= amount
I may need to tell the macro if
check (c) or deposit (d)
if (c) match on column A
if (d) match on column B and C

i need to highlight the cells that match in both sheets.

thanks,

VB coding
 

you can still use conditional formating. I usully get the formula working
correctly on the worksheet and then convert to conditional formating.

You can use countif for a simple column to column comparision
I you are in sheet 2 cell A5 comparing again sheet 1 colun A

=IF(COUNTIF(Sheet1!$A:$A,A5)0,TRUE,FALSE)

for two column comparision use sumproduct

=if(Sumproduct(--(sheet1!B1:B1000=B5),--(sheet1!C1:C1000=C5))0,True,True)


"ricky" wrote:

i am comparing two sheets.
checks = (sheet1.column A) looking for a match in (sheet2.column A)
Deposits = (sheet1.column B and C) looking for a match in (sheet2.column B
and C)

thanks,
"Joel" wrote:

You don't have to use a macro. Using conditional formating could give you
the same results

With the check you are looking for in Row 1 and the list of checks in Row 3
to 100

Add the formula in cell A3 inusing Format - Conditional formating and then
change Value IS to Formula IS

=And($A$1=$A3,$B$1=$B3,$C$1=$C3)

the in conditional formating choose the Pattern Tab and select a color.

Then copy cell A3 and select the rest of the table A3:C100 and use
PasteSpecial and coose Format. When you change cells A1:A3 the rows that
match will automatically hightlight.
"ricky" wrote:

i am trying to do a bank rec where i match on:
check number (exact match)
deposit amount and date (both field need to match)
Column A = check #
column B = date
column C= amount
I may need to tell the macro if
check (c) or deposit (d)
if (c) match on column A
if (d) match on column B and C

i need to highlight the cells that match in both sheets.

thanks,