Fill alternate cells macro, all sheets

cgmojoco

Well-known Member
Joined
Jan 15, 2005
Messages
696
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-
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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

Well-known Member
Joined
Jan 15, 2005
Messages
696
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
 

a7n9

Well-known Member
Joined
Sep 15, 2004
Messages
696

ADVERTISEMENT

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:
Code:
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

Well-known Member
Joined
Jan 15, 2005
Messages
696
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

Well-known Member
Joined
Sep 15, 2004
Messages
696

ADVERTISEMENT

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

Well-known Member
Joined
Jan 15, 2005
Messages
696
a7n9 said:
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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Try this,

Code:
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

Well-known Member
Joined
Jan 15, 2005
Messages
696
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..
 

Watch MrExcel Video

Forum statistics

Threads
1,119,273
Messages
5,577,144
Members
412,769
Latest member
VK12345
Top