I am at the end of a long macro I have been working on (thank you for all of those who have helped) and on the final page which sumarizes all of the information I would like to apply some conditional formatting to help the audience see what is going on. I have a column of percentages that start at J41 and run down several thousand rows (total amount will vary each time this report is run). The percentages are sorted so that they start with the "highest" negative percentage, runs through zero and goes up to the highest positive percentage. I would like to apply color scales using conditional formatting but the exact type that I need does not seem to be available so I need to work around it by searching the column for a specific range of values and applying the formatting to that range. Basically, what I need to do is apply the "Green, Yellow, Red Color Scale" to all values under 0, , fill solid green to all values equal to 0, then the "Red, Yellow, Green Color Scale" to all values above 0.
What I think would work best is a command which searched the column starting at J41 and selected all values >0, then applied the three color scale of Green, Yellow, Red; then searched for all values equal to 0 and filled the cells with solid green, then searched for all values above 0 and applied the three color scale of Red, Yellow, Green. I can do a Record Macro to get the steps for the formatting, but I do not know how to search a column for a range of values and select them to apply the formatting to. The end result should start at red for the most extreme negative percantage and gradually turn to a light green as it approaches zero, then zero itself would be solid green, then all values above zero would start out at a light green and progressively go to red. Any help would be appreciated.
What I think would work best is a command which searched the column starting at J41 and selected all values >0, then applied the three color scale of Green, Yellow, Red; then searched for all values equal to 0 and filled the cells with solid green, then searched for all values above 0 and applied the three color scale of Red, Yellow, Green. I can do a Record Macro to get the steps for the formatting, but I do not know how to search a column for a range of values and select them to apply the formatting to. The end result should start at red for the most extreme negative percantage and gradually turn to a light green as it approaches zero, then zero itself would be solid green, then all values above zero would start out at a light green and progressively go to red. Any help would be appreciated.
