prabhafriend
12-12-2009, 07:55 AM
Today we had to solve a very peculiar but seemingly very very easy scenario but its not as we thought. Actually the task we assigned is to check a workbook for filled cells i.e filled by any color. If there is a filled one then we have to report 'no' in a statement. Yeah initially i also thought the same idea. Going cell by cell and evaluating its colorindex. To say you the truth i hates this idea. Actually i want to achieve this in a smartest way possible. First i considered about the 'find and replace' functionality. I started to record a macro entered no string in the text to find and selected the 'no fill' in the formats. I eagerly expected to find an expression like this 'where cell.fill color = none' and dreamed about changing the operator to '<>' and solving the problem. But i never imagined that the find function is depending upon an object call findformat and its properties like interior and fill format like all. Actually it first assigns the object with fillcolour constant before calling the find function thus i cant assign a value with '<>'. I still believe there must be a smarter way other than looping each and every cell of the workbook. Thanks in advance.