Sorting and conditional formatting

Hello,
I am trying to do sorting and conditional formatting of two tables. They are presented in the atached example in sheet "Results". In short, with the help of several comboboxes, data from sheet "Source data" is filtered and copied in sheet "Results". So this is where we start from. Already copied ranges in "Results" should be sorted and formatted conditionally. Keep in mind that the number of rows and the data will vary according to the change in comboboxes.
Goals for the sorting:
1. The left table should be sorted by its last column (Growth N), the right table should be sorted by its last column (Delta N). Sort type - descending.

Goals for the conditional formatting:
1. Conditional formatting should be applied to the already sorted tables.
2. The rows of left and right table should be colored according to the following rules.
3. Formatting should be applied independently to the left and right table.
4. Top 25% of the rows should be colored in green (except headings). Last 25% of the rows should be colored in red. The middle 50% of the rows should be colored in orange.
5. If the number of the rows in the top 25% rows is not integer, then apply round function . If the result is round up, then increase the number of the rows in the top and last 25% and decrease them in the 50%. (For exmple, with 7 rows, top 25% - 2 rows, middle 50% - 3 rows, last 25% - 2 rows. If the result is round down, then increase the rows in the middle 50%. (For example, with 5 rows, top 25% - 1 row, middle 50% - 3 rows, last 25% - 1 row). But with 6 rows I suppose it is fair to add more rows to the middle 50%, despite that the round is up.
6. If we have only one row to format, let's make it green. If we have two rows, the first is green, the second is red. If we have three rows, then the first is green, the second is orange and the third is red.

If you have any suggestions, you are welcomed.

Thank you

Hi

The effects you are looking for with conditional formatting are a little awkward to achieve, however in VBA it is possible.

The revised spreadsheet with VBA code is attached and I think covers what you are looking for. To view the code right click on the results tab and click view code.

To use different colors just change the values for colors 1, 2 & 3.

Regards

Jeff

Thank you very much, solnajeff. I appreciate your help. There was a small error in your code. I fixed it. I also made some adjustments to the colors for my purposes. I atached the corrected file.

And may I dare to ask for additional help? If the moderators decide, I can move these addititonal questions to a new thread. They are related to the same example. The questions:
1. Let us assume that we want to see from the tables in sheet Results only the top three and the last three rows of them. And we want to see them in the form of two new tables above the old tables. I atached the same file with a new sheet called Demo. In Demo I put six examples depending of how many rows we have in lower tables. Examples 2-6 describes how the upper tables should look when the lower tables contain less than 7 rows. Sheet Demo provides only illustrations.
2. The same descending sorting should be applied. Top three rows are colored in green and the last three in red (see 1. for more detailed explanation). We will have two new tables (left and right) with the headings, then top three rows and last three rows.
3. I suppose that in these new conditions the old tables in sheet Results may start from row 20. And the new tables should start from row 6.
4. All this should depend on the filtering of the comboboxes.
5. The contents of upper tables are part of the lower tables.

I tried to do point 3, but I don't know how exatly to apply conditional formationg to the new postition of the old tables. The conditional formating is applied to the old position by the VBA code. I was looking into this part of code for the solution:

Let a = Application.CountA(Columns(1))
Let b = Round(a * 0.25, 0)
Let c = Application.CountA(Columns(5))
Let d = Round(c * 0.25, 0)

Thank you