+ Reply to Thread
Results 1 to 11 of 11

How to highlight the cells using a formula with conditional formatting or else

  1. #1
    Registered User
    Join Date
    02-05-2022
    Location
    Bulgaria
    MS-Off Ver
    Microsoft 365 apps for enterprise
    Posts
    37

    How to highlight the cells using a formula with conditional formatting or else

    Hi Team,

    I have a specific worksheet with bunch of unique lets say serial numbers. Each serial number has it's own description.
    The serial numbers are in Column A
    The description is in Column B.

    1) I have added Column C with a Vlookup to check the serial numbers that I am getting from another worksheet on a weekly basis and copied them into Sheet1.
    The reason is when I have the serial numbers that are matching from Sheet1 with the ones in sheet "Test" those serials need to be highlighted in Green but the problem is that I need the highlighted area to be with all the information I have for this serial number. For example the first serial in my list is 75E395X
    I need all rows from Row 2 to Row 16 to be highlighted in Green if this serial is found with the Vlookup formula.

    Another condition I need is probably more complicated and I really don't know how to start.

    2) Lets take the second serial number 75E3A4M. If for this serial number with matching values from row B at least 3 times Test1 at least 3 times Test 2 and at least 3 times Test3 again to highlight the area for this serial number in Green which is Row 17 to row 31 included. If this can be made we can skip point 1 as it will be the same.

    and the last condition I need is:

    3) If for a serial number it doesn't see Test3 in Column B but sees at least 3 Times Test1 and at least 3 Times Test 2 to highlight the area in yellow. Lets take Serial: 75E3A4Y
    for example and in this case the rows to highlight are Row 32 to Row 41 included.

    Does that make sense? Thank you in advance! I am making this by hand one be one and it takes me like 3 hours to complete this worksheet and the serial numbers will get more and more and I want to know if there is a chance to shorten this with formulas!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: How to highlight the cells using a formula with conditional formatting or else

    I will NOT open a .zip file, sorry. We need a SAMPLE workbook, not your entire dataset.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    02-05-2022
    Location
    Bulgaria
    MS-Off Ver
    Microsoft 365 apps for enterprise
    Posts
    37

    Re: How to highlight the cells using a formula with conditional formatting or else

    Hi Ali,

    It is a sample worksheet not the entire one, but it's more than 1mb so I cannot attach it...

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to highlight the cells using a formula with conditional formatting or else

    I opened the .zip file and it seems to me that it would be rather easy to comply with AliGW's post.
    From the description in post #1 it seems that all components of your request could be illustrated in rows 1:41 of the Test sheet.
    Since the first two serial numbers on the Test sheet have a match, and the third doesn't, it would seem that you only need to include those two and perhaps one non-matching serial number on Sheet1.
    From there please apply the desired highlighting manually so that we can compare it to the results of our proposed formulas/code.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    02-05-2022
    Location
    Bulgaria
    MS-Off Ver
    Microsoft 365 apps for enterprise
    Posts
    37

    Re: How to highlight the cells using a formula with conditional formatting or else

    Following your suggestions I have deleted everything till row 42 and the excel sheet is still 2.67mb... I also deleted all machines in sheet1 except for the first 2.
    I don't know how to resize the excel to be less than 1mb...I have uploaded again the .zip with the already colored pieces if someone can help with the .zip.
    I also deleted the vlookup formula in order to resize it but it didn't even go down with 1kb
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-05-2022
    Location
    Bulgaria
    MS-Off Ver
    Microsoft 365 apps for enterprise
    Posts
    37

    Re: How to highlight the cells using a formula with conditional formatting or else

    OK, I opened a brand new excel and wrote the things inside by hand not copy paste and it is very short now. Please find it attached.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to highlight the cells using a formula with conditional formatting or else

    Thank You.
    This proposal uses the following formula for cells C2 and down: =IF(ISNUMBER(LEFT(A2,1)+0),IFERROR(VLOOKUP(A:A,Sheet1!A:A,1,FALSE),"N/A"&A2),C1)
    In the interest of keeping your file size low I suggest applying green to all cells and then overfilling using the following conditional formatting rule for yellow: =LEFT(C2,1)="n"
    The rules for Test2 and Test3 are similar to: =AND(B2="Test2",COUNTIFS(C$2:C$41,C2,B$2:B$41,"Test2")>=3)
    Note that the formulas in columns E:G are for demonstration purposes only.
    Let us know if you have any questions.
    Attached Files Attached Files
    Last edited by JeteMc; 09-02-2022 at 09:16 AM. Reason: Updated formulas

  8. #8
    Registered User
    Join Date
    02-05-2022
    Location
    Bulgaria
    MS-Off Ver
    Microsoft 365 apps for enterprise
    Posts
    37

    Re: How to highlight the cells using a formula with conditional formatting or else

    Hi JeteMc,

    this is perfect solution! When I wrote my initial questions I wanted to have a formula so I can use it by the example you will give me not to waste your time,
    but when I see what you used I don't think I can use them for my other elements. Let me give you all conditional formatting rules to see if we can use them for the
    same purpose. With the N/A in front it works great but I will have 3 more examples where the color should be different and we will still have N/A.

    1) If it sees in Column B at least 3 times Test1, at least 3 times Test2 and at least 3 times Test3 to make all cells from A2 to A31 in green.
    2) If it sees in Column B at least 3 times Test1 and at least 3 times Test2 without anywhere Test 3 to make cells A32 to A41 (as in the example you gave me)in yellow.
    3) If it sees in Column B at least 3 times Test3 without anywhere Test1 and Test2 to make Cells A42 to A45 in red
    4) If it sees in Column B at least 3 times Test3, less then 3 times Test2 and without Test1 to make cells in blue.

    the rest that are left in white I will make them in orange so no need to make conditional formatting for them.

    I hope I explained it well I have attached the example sheet with the colors already there.

    Thank you very much!
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to highlight the cells using a formula with conditional formatting or else

    This proposal employs eight helper columns (I:P) which may be moved and or hidden for aesthetic purposes.
    Column I is populated using: =IFERROR(INDEX(C$2:C$51,MATCH(0,INDEX(COUNTIF(I$1:I1,C$2:C$51),,),)),"")
    Columns J:L are populated using: =IF($I2="","",SUMPRODUCT(($C$2:$C$51=$I2)*($B$2:$B$51=J$1)))
    Column M is populated using: =IF(MIN(J2:L2)>=3,I2,"")
    Column N is populated using: =IF(AND(MIN(J2:K2)>=3,L2=0),I2,"")
    Column O is populated using: =IF(AND(J2=0,K2=0,L2>=3),I2,"")
    Column P is populated using: =IF(AND(L2>=3,K2<3,J2=0),I2,"")
    The conditional formatting rules are:
    For Red: =COUNTIFS(O$2:O$8,C2)
    For Blue: =COUNTIFS(P$2:P$8,C2)
    For Yellow: =COUNTIFS(N$2:N$8,C2)
    For Green: =COUNTIFS(M$2:M$8,C2)
    Let us know if you have any questions.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-05-2022
    Location
    Bulgaria
    MS-Off Ver
    Microsoft 365 apps for enterprise
    Posts
    37

    Re: How to highlight the cells using a formula with conditional formatting or else

    Thanks a lot!!! That works perfectly!

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to highlight the cells using a formula with conditional formatting or else

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. [SOLVED] Conditional Formatting Formula - highlight cells with different values within a criteria
    By L plates in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-20-2021, 05:40 PM
  2. Replies: 1
    Last Post: 02-04-2019, 03:47 AM
  3. Conditional Formatting Formula to highlight cells in a different sheet
    By Lemon01 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-06-2016, 11:27 PM
  4. Need a 'conditional formatting' formula to highlight cells between dates/text
    By BigSean762 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-29-2014, 02:33 PM
  5. [SOLVED] Can Conditional Formatting highlight cells containing any formula?
    By tuph in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-23-2014, 11:27 PM
  6. Replies: 5
    Last Post: 09-25-2014, 07:00 AM
  7. Replies: 0
    Last Post: 03-15-2005, 07:40 AM

Tags for this Thread

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