+ Reply to Thread
Results 1 to 25 of 25

Conditional formatting with condition

  1. #1
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Conditional formatting with condition

    Hello experts,
    I have posted this query in different forums and I am not able to solve it though. I am trying to color the rows where the amounts are repeated more than twice under one ID No. I am not even able to get the first condition of the 2 conditions right.
    I tried and entered this formula in the new rule to get the first condition right but it seems I am missing something.
    =COUNTIFS(C$2:C$20000,C2,G$2:G$20000,G2,H$2:H$20000,H2,I$2:I$20000,I2)>2
    The second condition is that it should accept the amounts where the difference is less than 1/- between the same amounts under one ID No. like in the workbook.

    The Link to the same query in the other forum.
    https://www.mrexcel.com/board/thread...ition.1212126/
    Attached Files Attached Files
    Last edited by RAJESH SHAH; 08-10-2022 at 02:39 PM. Reason: #Solved by JeteMc

  2. #2
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    As I haven't received any reply to my post which may be due to the migrating, FYI, I have shared this query on a different forum.
    https://forum.ozgrid.com/forum/index...36#post1256736

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,243

    Re: Conditional formatting with condition

    I cant see your rule in any cell, can you point me to where you have it?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    I tried the formula in the sheet but it didn't work. I need help with the formula. I have colored the cells manually to show the end expected result.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,243

    Re: Conditional formatting with condition

    OK what exactly are you testing for? Your formula seems to include testing in all columns, but what is the basic criteria?

  6. #6
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    Under each ID there are rows which contain certain amounts. I am trying to color the rows where under one ID the similar amounts appear more than twice and if the difference between the amount is 1/- ruppee, then too it should accept it as similar amount

  7. #7
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    Even a code will help if possible. Please Compare rows C, G, H and I only and color the rows from A: L
    Last edited by RAJESH SHAH; 08-04-2022 at 02:06 AM.

  8. #8
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    This is to inform you that the issue has not yet been resolved in any of the forums. If it is solved I will update this post without fail with the solution.

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

    Re: Conditional formatting with condition

    For the data shown in the file attached to post #1, use the following formula for the conditional formatting rule:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the applies to is: =$C$2:$L$23
    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.

  10. #10
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    JeteMc. That is perfect. Finally, after posting on different forums for the solution for nearly a week you finally solved it man.
    Thank you so much.

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

    Re: Conditional formatting with condition

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  12. #12
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Conditional formatting with condition

    Hello experts
    This formula was shared by JeteMc and it is working perfect. But to correct one issue, I need your help once again to edit the formula in such a way that when I apply conditional formatting it should color the rows which appear 3, 5 or 7 times only under one GSTIN. If the amounts under one GSTIN number appears 2,4,6, or 8 times I don't want to color it.
    The formula used earlier in the new rule was
    =SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))>2
    Attached Files Attached Files
    Last edited by RAJESH SHAH; 08-22-2022 at 10:07 AM.

  13. #13
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    JetMc. Can you please help me to change the count and edit the code if possible.? like if
    =SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3 & 5 & 7 & 9
    I am not sure how to place the condition here.

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,877

    Re: Conditional formatting with condition

    Taking in mind that your needs may change again I suggest:
    1. Make a table listing the number of repetitions to be highlighted
    2. Use the following as a conditional formatting rule:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that if you add to or delete from the table the formula will automatically adjust.
    Let us know if you have any questions.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    JeteMc. Is it possible to write a formula without the help column Q. The sheet I am trying to color the rows yellow is created after the code runs. So, I have to convert the formula into a code and then add that in the already existing code of the workbook. So, when the final code is played, it will create the sheet with the data and also color the cells Yellow.

  16. #16
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    If I have to apply multiple times CF for the same range then too it will be helpful.
    =SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3
    =SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=5
    and so on.

  17. #17
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    The name of the sheet in the original workbook is Combined Data and this is the code of the formula you shared before, which will help me to color the rows yellow which appear more than twice., sort the data and get me the yellow rows in the top.
    HTML Code: 

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,877

    Re: Conditional formatting with condition

    Your idea to apply CF multiple times to the same range (post #16) seems reasonable, however I don't know enough about VBA to say how to translate the formulas into code.

  19. #19
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    You can just share the perfect formula to color 3 rows, 5 rows. etc., The code is not a problem. I can get the formula converted into code.
    Getting the right formula is the tough part.

  20. #20
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    =SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3
    I tried by changing <2 to =3 it is working.
    =SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=5
    This is working too.
    Is it possible to replace =3 with odd numbers in a formula? Obviously except 1.

  21. #21
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    JeteMc. Hope you are still online.
    I tried to apply the formula like this
    =SUMPRODUCT(($C$2:$C$230=$C2)*($G$2:$G$230>$G2-1)*($G$2:$G$230<$G2+1)*($H$2:$H$230>$H2-1)*($H$2:$H$230<$H2+1)*($I$2:$I$230>$I2-1)*($I$2:$I$230<$I2+1))=3&5&7
    The formula is accepted without error but it is not coloring the cells yellow. Can you correct the end portion of the formula Please?

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,877

    Re: Conditional formatting with condition

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  23. #23
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    Eureka. Got it right. Thank you very much JeteMc.

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,877

    Re: Conditional formatting with condition

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  25. #25
    Forum Contributor
    Join Date
    11-25-2021
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    411

    Re: Conditional formatting with condition

    You too have a wonderful 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. Condition formatting of multiple condition formatted cells
    By merrin84 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-22-2015, 05:08 PM
  2. [SOLVED] Condition Formatting Help
    By ExcelDummy77 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-27-2014, 09:36 AM
  3. Condition formatting
    By sam51285 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2014, 05:27 AM
  4. Condition formatting
    By ExcelBG in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2013, 10:49 AM
  5. Using OR in Condition Formatting
    By SamuelT in forum Excel General
    Replies: 8
    Last Post: 11-07-2005, 11:25 AM
  6. I need 4 condition for condition formatting
    By SeeKY in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2005, 05:05 AM
  7. condition formatting
    By rocket0612 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-22-2005, 10:01 AM

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