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,
|