Remove colored cells from all worksheets 
Author Message
 Remove colored cells from all worksheets

I have a workbook with 4 worksheets with random cells that have interior
color yellow.

I'd like to remove the color yellow by stepping through all workshets in
code, but without having to visit each cell. Is there a way to do this
without having to select the entire document ?



Wed, 18 Jun 1902 08:00:00 GMT  
 Remove colored cells from all worksheets

Use Cells in your code. Cells represents ALL of the cells in the specified
workbook. Such as:

Cells.Font.Color = 17


Quote:
> I have a workbook with 4 worksheets with random cells that have interior
> color yellow.

> I'd like to remove the color yellow by stepping through all workshets in
> code, but without having to visit each cell. Is there a way to do this
> without having to select the entire document ?



Wed, 18 Jun 1902 08:00:00 GMT  
 Remove colored cells from all worksheets

Sub tester5a()
For Each sh In ThisWorkbook.Worksheets
   sh.Cells.Interior.ColorIndex = xlNone
Next
End Sub

This resets the interior color of every cell - regardless if it is yellow or
not.  If you only want yellow cells, you have to go cell by cell.

Regards,
Tom Ogilvy
MVP Excel


Quote:
> I have a workbook with 4 worksheets with random cells that have interior
> color yellow.

> I'd like to remove the color yellow by stepping through all workshets in
> code, but without having to visit each cell. Is there a way to do this
> without having to select the entire document ?



Wed, 18 Jun 1902 08:00:00 GMT  
 Remove colored cells from all worksheets



Quote:
>I have a workbook with 4 worksheets with random cells that have interior
>color yellow.

>I'd like to remove the color yellow by stepping through all worksheets in
>code, but without having to visit each cell. Is there a way to do this
>without having to select the entire document ?

If those are the *only* colored cells, then you can write

  For S = 1 To Worksheets.Count
    Worksheets(S).Cells.Interior.ColorIndex = xlNone
  Next S

OTOH, if there are other colored cells, say green or blue ones, and these
colors are to be kept, there's no alternative but to loop through all of the
cells in the used range on each sheet, checking the color and changing when
appropriate.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Removing Cell Colors ??

2. Color In Cells do not appear in Worksheet

3. Coloring Cells in worksheet

4. Linking a cell from one worksheet in a work book to a cell in another worksheet

5. Removing unwanted spaces from worksheet cells

6. remove blank cells in mutiple worksheets

7. Is there any way to get true colors as backround colors in cells

8. Shape Fill Color vs. Cell Color

9. Concatenate colored cells and preserve colors.

10. Color Cell Based on Interior Color; Not Value


 
Powered by phpBB® Forum Software © phpBB Group