Fill alternate cells macro, all sheets

[cgmojoco]

Would like a script that will go to each sheet in the workbook, and fill in light grey background, each alternate row that contains data, EXCLUDING the 1st row (header row).

Thanks in advance-

 

[Tazguy37]

How about conditional formatting?:

Format, Conditional Formatting, choose FormulaIs: and enter:
=AND(ROW()>1,MOD(ROW(),2)=0,NOT(ISBLANK(A1)))

Use the Format Painter to copy it to other cells. HTH

 

[cgmojoco]

I'm lost-

I tried, I really did!

When I did as you instructed it did not highlight (fill) the rows grey.

If it did, I would have recorded the script and I could have used the macro script.

Will someone please put this into a macro format?

OR will some kind hearted gent, elaborate on how to get the conditional formatting to work.

Thanks for your efforts in advance

 

[cgmojoco]

Nodoby is going to drop me a holiday present?

lol

 

[a7n9]

In order to get what Tazguy37 has suggested
1. Select the data range
2. Select Format>Conditional Formatting
3. Below Condition 1, select Formula Is from the Drop down and enter the formula Tazguy37 gave.
4. Click on Format and Select the color you want and hit ok.

I recorded this:

Sub test()
    Selection.CurrentRegion.Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(ROW()>1,MOD(ROW(),2)=0,NOT(ISBLANK(A1)))"
    Selection.FormatConditions(1).Font.ColorIndex = 15
End Sub

 

[cgmojoco]

Oh right, I just learned that you have to make a selection first!

Well now, how can I get this to go through all the sheets automatically?

I.e. The range is all rows up to the last row with any data in it, on each sheet....excluding sheet x

Thanks for the progress and the edumacation

 

[a7n9]

Try this:
<font face=Courier New><SPAN style="color:#007F00">'---------------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">' Procedure : Do_Cond_Format</SPAN>
<SPAN style="color:#007F00">' DateTime  : 12/23/2005 12:25</SPAN>
<SPAN style="color:#007F00">' Author    : Ashutosh</SPAN>
<SPAN style="color:#007F00">' Purpose   : To format all the sheets excluding sheetX</SPAN>
<SPAN style="color:#007F00">'---------------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Do_Cond_Format()

   <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Do_Cond_Format_Error
    <SPAN style="color:#00007F">Dim</SPAN> wksht <SPAN style="color:#00007F">As</SPAN> Worksheet, lastcol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wksht <SPAN style="color:#00007F">In</SPAN> Worksheets
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wksht.Name = "sheetx" <SPAN style="color:#00007F">Then</SPAN>
            lastcol = wksht.Range("IV1").End(xlToLeft).Column
            lastrow = wksht.Range("A65536").End(xlUp).Row
            <SPAN style="color:#00007F">With</SPAN> wksht
                .Range(.Cells(2, 1), .Cells(lastrow, lastcol)).Select
                Selection.FormatConditions.Delete
                Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                    "=AND(ROW()>1,MOD(ROW(),2)=0,NOT(ISBLANK(A1)))"
                Selection.FormatConditions(1).Font.ColorIndex = 15
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> wksht

   <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
   <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

Do_Cond_Format_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Do_Cond_Format"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


</FONT>

 

[cgmojoco]

Try this:
<font face=Courier New><SPAN style="color:#007F00">'---------------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">' Procedure : Do_Cond_Format</SPAN>
<SPAN style="color:#007F00">' DateTime  : 12/23/2005 12:25</SPAN>
<SPAN style="color:#007F00">' Author    : Ashutosh</SPAN>
<SPAN style="color:#007F00">' Purpose   : To format all the sheets excluding sheetX</SPAN>
<SPAN style="color:#007F00">'---------------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Do_Cond_Format()

   <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Do_Cond_Format_Error
    <SPAN style="color:#00007F">Dim</SPAN> wksht <SPAN style="color:#00007F">As</SPAN> Worksheet, lastcol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wksht <SPAN style="color:#00007F">In</SPAN> Worksheets
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wksht.Name = "sheetx" <SPAN style="color:#00007F">Then</SPAN>
            lastcol = wksht.Range("IV1").End(xlToLeft).Column
            lastrow = wksht.Range("A65536").End(xlUp).Row
            <SPAN style="color:#00007F">With</SPAN> wksht
                .Range(.Cells(2, 1), .Cells(lastrow, lastcol)).Select
                Selection.FormatConditions.Delete
                Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                    "=AND(ROW()>1,MOD(ROW(),2)=0,NOT(ISBLANK(A1)))"
                Selection.FormatConditions(1).Font.ColorIndex = 15
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> wksht

   <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
   <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

Do_Cond_Format_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Do_Cond_Format"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


</FONT>

Well I gave it a shot (thank you very much for trying to help me BTW) and ran into a few problems:
1- I receive the following error message- " Error 1004 (Select method of Range class failed) in procedure Do_Cond_Format"- , this stops the script from carrying on any further.

2-It changes text color rather than fill color of the cell

3-It only works on the selected sheet?

 

[Krishnakumar]

Hi,

Try this,

Sub Test()
Dim ws  As Worksheet
For Each ws In Worksheets
    Application.ScreenUpdating = False
    ws.Activate
    With [a2].CurrentRegion.Select
        Selection.FormatConditions.Delete
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=AND(ROW()>1,MOD(ROW(),2)=0,NOT(ISBLANK(A1)))"
            Selection.FormatConditions(1).Interior.ColorIndex = 15
    End With
Next ws
Application.ScreenUpdating = True
End Sub

HTH

 

[cgmojoco]

How can I make this work on column A (i.e. this is not greying the entire row....just the cells in the row that have data)

Instead I would like it to grey based on wether cells in column A have data...that way if some cells in other columns don't have data...they still get greyed..