+ Reply to Thread
Results 1 to 27 of 27

Conditional Formatting Entire Rows based on Change in Cell Value in Column A

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Angry Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Hi all,

    I am new to this forum so please bare with me if this has already been covered but I'm really stuck for some pretty urgent help.

    I'm doing a huge amount of stock sheets for our year end in work & I need to highlight rows based on change in my PLU. Eg...

    MAC1516 UNIVERSAL COUPLING GAUZE - MSG/MSP 908002 NS H2C1
    BAU1062 HOSE RUBBERS QCR QCR NS H2C2
    BAU1045 3/4" CLAW CLAMP CA34Q195-DL NS H2D3
    BAU1045 3/4" CLAW CLAMP CA34Q19S/DL NS H2D3
    BAU1045 3/4" CLAW CLAMP H0238102 NS H2D3
    MIS1749 STAND PIPE SEAL, BAYONET 307BAY-SEAL NS H2E2

    So I'd need to have the row with MAC1516 just clear & then say BAU1062 shaded grey and then the rows with BAU1045 clear again, etc...

    Could someone PLEASE help me????
    Last edited by Etain; 09-26-2012 at 05:35 AM. Reason: Typos

  2. #2
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Hi and welcome to the forums!

    If you highlight all the data you want included. When you go into conditional formatting select 'Formula is' from the dropdown box and then type.

    Please Login or Register  to view this content.
    Obviously you need to choose what formatting you want. I have assumed you know how to do this. Just repost if you don't

    Hope that does it for you
    Last edited by DannyJ; 09-26-2012 at 05:46 AM. Reason: Added more information

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Hi DannyJ,

    No, that didn't work!! I am using Office 2010 & don't have a dropdown box so that might be why. I can enter a formula alright.

    Thanks a mil for the quick reply & yes I know about the formatting bit, just can't get it to change when I need to!

    Sorry to be a nuisance!!
    Last edited by Etain; 09-26-2012 at 05:56 AM.

  4. #4
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    When you click on the 'Conditional Formatting' box. Click new rule and you should be able to input the formula I gave you. (There should be an option in the 'Create new rule' it's called 'Use a formula to determine which cells to format')

    You aren't been a nuisance, I should have checked which version you were using! haha

    Regards

    Danny
    Last edited by DannyJ; 09-26-2012 at 05:59 AM.

  5. #5
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    I should have said in my original post. Nope, that ain't working either! :-(

  6. #6
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    What's the problem? Is the formula not being accepted or it isn't changing what you need it to? Is it changing column A and not the rest?

  7. #7
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    It's not changing anything at all but it isn't giving me an error either.

    I think the complication is because say if I have one of MAC1516, two of BAU1062 and could have four of another maybe?

  8. #8
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    No that shouldn't cause an issue. Have you told it to change the colour of the cell? and do you have all the data in your table highlighted?

    Could you perhaps put some sample data in a spreadsheet for me to use please?

  9. #9
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    I've attached a sample... thanks a million, you're a star! :-)

    Yes, I told it to change the colour of the cell. I got it to change the 2nd row but that was all...

    Thanks again Danny!
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    The formula I gave you simply searches for "BAU1062" which I am starting to think is perhaps incorrect? What items need to be highlighted? Is it certain words or is it certain differences?

    If you could give me exact criteria please then I will make sure that I have given you the correct formula.

    Regards

    Danny

  11. #11
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Sorry I probably didn't explain myself too well.

    If you look at the sample I attached, I'll have over 50,000 rows like this. MIS1749 is a code for a product which may have multiple part no's (see Column C). So, essentially at each change in my code (Column A) I need to highlight the rows based on each change. Row 1 would be white & rows 2 & 3 would be grey, 4, 5, 6 & 7 would be white. Do you know what I mean, sorry I know it's a bit confusing & a pain!

  12. #12
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Sorry, I am struggling to get my head around this one. haha

    Why would rows 2 & 3 be grey? Because they have the same code for the product but different part no's?

    Sorry about this!

    Danny

  13. #13
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Yes, exactly!

    You're fine, I know it's a confusing one! We are doing a stock take & everything with the one code will be in the one location so I need to highlight this on the printout.

    Thanks!

    Etain.

  14. #14
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    So you want to highlight all the 'BAU1346s' and then all the 'BAU1032s' after that etc. etc.?

    I have attached a picture of what I think you want.

    Danny
    Attached Images Attached Images

  15. #15
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    is this what you mean?
    Attached Files Attached Files
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  16. #16
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Yes Danny, that'd be what I'd need! :-)

  17. #17
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Hi Joseph,

    Yeh, that seems to be it too. Tried it on my whole workbook though & it was a bit hit & miss the further I went down the page.

    Sorry, I will be wrecking your heads!!

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    perhaps a small adjustment

    =MOD(INT(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1))),2)=1

  19. #19
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    What is that Formula doing Joseph? My understanding is that the conditional formatting just needs to search for any duplicates, is that correct Etain? If that is the case could you not just use

    Please Login or Register  to view this content.
    Once you have put the in row A only then copy row A and highlight the rest of the worksheet and right click and paste special. Then select Format and OK. Hopefully that should work for you. If you need what I think?

  20. #20
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    it's counting how many unique codes have appeared in the column (so two rows of the same code are counted as 1) and then highlighting each alternate band of codes. so the first set of codes gets highlighted, the second set doesn't then the third does and so forth

  21. #21
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    No Danny, it wouldn't just be looking for duplicates as I could have lots of single rows too.

    JP the formula you gave me is running on my worksheet now, it's huge so may take a while. Fingers crossed!!

  22. #22
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    for a large spreadsheet I'd put the formula in a column and refer to that result in the cf instead-it oughta be a lot more efficient than having all the cells evaluate it

  23. #23
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    What do you mean the cf?? Sorry, brain has gone to mush this morning!

    That seems to be doing the job though, I broke it down into rows of 500 as it was stalling on me when I selected the whole worksheet.

    Thanks sooooo much, you saved me so much time!!!!

    Than you too Danny!!!

  24. #24
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    cf = conditional formatting ;-)

    please don't forget to mark the thread solved if the solution works.
    Last edited by JosephP; 09-26-2012 at 07:24 AM.

  25. #25
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Sorry... totally blonde moment! I'm all conditional formatting out!!!

    How would I refer it to that then? just say = cell where I put the formula?

  26. #26
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    yes-just put
    =$A1
    in the cf dialog

    note that in a cell that formula has to be array-entered with ctrl+shift+enter. see sample
    Attached Files Attached Files
    Last edited by JosephP; 09-26-2012 at 07:30 AM.

  27. #27
    Registered User
    Join Date
    09-25-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Conditional Formatting Entire Rows based on Change in Cell Value in Column A

    Brilliant, thanks again.... so so soooo much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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