I need help to determine what is the most efficient way to format alternate rows with a different color. I've got my entire spreadsheet formatted as desired (some cells left-justified, some cells centered, correct number of decimal places, ranges with borders set appropriately, etc.). Each cell is formatted by using the appropriate style that I create manually (e.g., LeftJustifyWithPercent, RightJustifyWith2DecimalPlaces, RightJustifyWithPercent, etc.). None of these styles specify a foreground color.
If I simply cycle through the even rows to a new foreground color to the range of cells contained within that row, the original formatting is lost. If I simply cycle through the rows and change the foreground color of the cell's style, it appears that I've actually altered the definition of the original style object, thus all cells that reference that style are set to the alternate row color.
Is there a specific order of formatting that can be applied or some tip/trick/technique that can be used without creating a separate alternate row style for each different format used within the spreadsheet? Having to create a separate style object for both row colors would be very frustrating.
The code you've suggested works. However, setting the foreground color of a row causes any pre-existing formatting to be removed. What I'm looking for is really an option to apply only a foreground color to a row without harming any of the existing formatting.
Your code did successfully set the row color, however, it destroyed all cell-level formatting that was in place in my designer spreadsheet (e.g., justification, date and percentage formets, etc.).
I've worked around the issue successfully by doing the following:
- at runtime, add the requisite number of rows to hold my data (they're now all white background).
- create a range that holds two fully formatted rows elsewhere in my designer spreadsheet (one row white, one row of my alternate color). These rows are fully formatted with justification and cell format types.
- then, cycle through the new rows two at a time, copying the style of the aforementioned range to the rows I'm iterating through.
- then, cycle through the rows one at a time to add my data.
This works, but it is sure not straightforward! I was really just hoping for api call that would set the foreground color of a row to my desired color without destroying any existing cell formatting. I probably could do this if I used the technique you described in your previous post, but I would have had to cycle through each and every cell rather than operating on a row-by-row basis.