I've implemented a function which, given a selection of cells, color codes them based on formulas. This is fairly fast, because we can retrieve the formula information, and then write the color data to the sheet in batches (one batch for each color, subject to the constraint that the range function has a 255 character limit on argument).
The end user might then want to recover their existing formatting (e.g., if their excel sheet is already color coded, it is helpful to spot mistakes in formulas using my function, but they won't want to lose the existing colors). For this, I'd have to be able to retrieve the existing color information before executing my function.
However, I cannot work out how to do this efficiently - it seems that we can only get color information from a cell using mysheet.range(...).color=... or .api.Interior.ColorIndex one cell at a time, which would then require iterating through the entire range of cells and accessing their colors. This is then quite slow for large ranges, as it requires interacting with excel once for each cell.
Is it possible to get cell color information in batches/minimize the number of calls to excel? Or are there other potential workarounds?
