# Conditional formatting with condition

1. ## 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.

2. ## 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. ## Re: Conditional formatting with condition

I cant see your rule in any cell, can you point me to where you have it?

4. ## 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. ## 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. ## 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. ## 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

8. ## 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. ## 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:
`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.

10. ## 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. ## 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. ## 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

13. ## 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. ## 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:
`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.

15. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Conditional formatting with condition

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

Let us know if you have any questions.

23. ## Re: Conditional formatting with condition

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

24. ## 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. ## Re: Conditional formatting with condition

You too have a wonderful day.

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

#### 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