+ Reply to Thread
Results 1 to 9 of 9

if the data match to the other sheet, color the row on that sheet.

  1. #1
    Registered User
    Join Date
    02-15-2014
    Location
    metro manila, philippines
    MS-Off Ver
    Excel 2007 & 10
    Posts
    43

    if the data match to the other sheet, color the row on that sheet.

    Hi ExcelForum members,

    I am newbie on macro. I just doing macro sometimes by using recording macro then stop. I really don't how to code manually.
    I have a problem on my workbook. My boss want a format that every time my staff record receipts from sales on account, it will highlight the row which customer paid on the other sheet which is the summary of 'Sales on Account'.

    For clear view of my problem, here's my file trial.xlsm.

    You can see there the sheet name; Total and Receipts.

    The Total(sheet) contains summary of sales. The Receipts(sheet) contains payment of customers. Every payment recorded in the sheet Receipts, it will highlights the row of customer on the table located at sheet Total.

    Please help me

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: if the data match to the other sheet, color the row on that sheet.

    Follow Below Steps
    1 GO to formula tab ---> Name Manager--> Give name like Color ---> in formula bar copy paste =SUMPRODUCT(--(Receipts!$C$6:$C$13=Total!$C6))=1 then close tab
    2.select A6 to D18
    2. go to conditional formatting --> new rule -- > determine a formula to CF --> Click in the formula bar press F3 and select color
    3. select the color you want from format tab below click Ok
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    02-15-2014
    Location
    metro manila, philippines
    MS-Off Ver
    Excel 2007 & 10
    Posts
    43

    Re: if the data match to the other sheet, color the row on that sheet.

    Hi hemesh,

    it does'nt work for me i don't know i fail on following your instruction but every time i input data on sheet receipts, the total(sheet) doesn't change.

    do you know codes to macro that could do that?

  4. #4
    Registered User
    Join Date
    02-15-2014
    Location
    metro manila, philippines
    MS-Off Ver
    Excel 2007 & 10
    Posts
    43

    Re: if the data match to the other sheet, color the row on that sheet.

    Hi hemesh,

    I miss one step on your instruction.
    It works for me now.

    Thanks for your help!

    Cheers!

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: if the data match to the other sheet, color the row on that sheet.

    That's Cool ! You can change the references if data range changes i.e. $C$6:$C$13 to $C$6:$C$100 or as per data

  6. #6
    Registered User
    Join Date
    02-15-2014
    Location
    metro manila, philippines
    MS-Off Ver
    Excel 2007 & 10
    Posts
    43

    Re: if the data match to the other sheet, color the row on that sheet.

    haha! ok buddy

    thanks again for your help ^^

  7. #7
    Registered User
    Join Date
    02-15-2014
    Location
    metro manila, philippines
    MS-Off Ver
    Excel 2007 & 10
    Posts
    43

    Re: if the data match to the other sheet, color the row on that sheet.

    Hi hemesh,

    What if the data doesn't match to the list. I want to mark the row data as red to warn my staff that she doesn't type the invoice number correctly.

    Thanks for advance buddy

  8. #8
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: if the data match to the other sheet, color the row on that sheet.

    Either you can use
    Follow Below Steps
    1 GO to formula tab ---> Name Manager--> Give name like Colorwrongentry ---> in formula bar copy paste =SUMPRODUCT(--(Receipts!$C$6:$C$13=Total!$C6))=0 then close tab
    2.select Reciepts SHeets ! A1:E13
    2. go to conditional formatting --> new rule -- > determine a formula to CF --> Click in the formula bar press F3 and select colorwrongentry
    3. select the color you want from format tab below click Ok

    Or use
    1 GO to formula tab ---> Name Manager--> Give name like Colorwrongentry ---> in formula bar copy paste
    =IF(C6<>"",ISNUMBER(MATCH(C6,Total!$C$6:$C$18,0)),"")
    then close tab
    2.select Reciepts SHeets ! A1:E13
    2. go to conditional formatting --> new rule -- > determine a formula to CF --> Click in the formula bar press F3 and select colorwrongentry
    3. select the color you want from format tab below click Ok

  9. #9
    Registered User
    Join Date
    02-15-2014
    Location
    metro manila, philippines
    MS-Off Ver
    Excel 2007 & 10
    Posts
    43

    Re: if the data match to the other sheet, color the row on that sheet.

    Thanks buddy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. look up on sheet 1 match data sheet 2 and fill in cell on first sheet
    By treborharris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-28-2013, 01:01 PM
  2. Replies: 1
    Last Post: 03-11-2013, 05:37 AM
  3. Replies: 11
    Last Post: 10-14-2012, 01:03 PM
  4. Replies: 4
    Last Post: 08-29-2012, 10:32 AM
  5. Replies: 1
    Last Post: 02-22-2006, 03:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1