Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum by coding without 0 | New Users to Excel | |||
HELP for coding? | Excel Programming | |||
Can anyone help me with coding this? | Excel Programming | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming |