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). 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. (Just in case someone knows how to do this in shorter steps, 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 formatting, then searched for all values equal to 0 and applied formatting, then searched for all values above 0 and applied formatting. 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. Any help would be appreciated.