Automatic colors?

[mrkris1982]

Is there anyway I can hard-code the color of each row?

Id like to alternate white with tan (office 2003) if possible.

The shared workbook we use now is colored manually. When pasting, the formats paste as well and I wonder if hard coding it will prevent the format from being pasted. I DO realize you can paste special, but I am not going to put in the time to show that to the employees using the workbook since I could just go back to an all WHITE workbook.

Thanks

 

[kpark91]

Try:

Private Sub FillOnlyOddRows()
    Dim n As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
    For c = 2 To Range("A" & Rows.count).End(xlUp).Row
    
        If Rows(c).Hidden = False Then
            n = n + 1
            
            If n Mod 2 = 1 Then
                Rows(c).Interior.Color = RGB(220, 246, 255)
            Else
                Rows(c).Interior.Color = vbWhite
            End If
            
        End If
        
    Next c
    
    Application.ScreenUpdating = True
    
End Sub
 
Private Sub Worksheet_Calculate()
        Call FillOnlyOddRows
End Sub

You can change RGB (220, 246, 255) to any colors you want, whether it's tan, red, or anything...

So, when you mean pasting, you don't want the colors in the pasted cells?

Will this paste occur in another worksheet?

If then, you could try conditional formatting. (haven't tried it tho)

 

[mrkris1982]

This works pretty good!

What would the code look like if I wanted this to only effect C2:J1999?

Thanks

 

[mrkris1982]

The reason I ask is because I would like to hide all the cells I am not using (everything after column J) and if I have colors in those cells, I might get an "CANNOT SHIFT OBJECTS OFF SHEET" Error.

 

[lenze]

Select C2:J1999 and in CF use

=MOD(ROW()-2,2)+1<=1

See Chip Pearson's discussion on "Color Banding"
http://www.cpearson.com/Excel/banding.aspx
lenze

 

[mrkris1982]

Sorry for being a novice with some of the acronyms, but what is CF?

I know to select the range of cells I want, but not sure where your code should be placed in the existing code:

Private Sub FillOnlyOddRows()
    Dim n As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
    For c = 2 To Range("A" & Rows.Count).End(xlUp).Row
    
        If Rows(c).Hidden = False Then
            n = n + 1
            
            If n Mod 2 = 1 Then
                Rows(c).Interior.Color = RGB(153, 204, 255)
            Else
                Rows(c).Interior.Color = vbWhite
            End If
            
        End If
        
    Next c
    
    Application.ScreenUpdating = True
    
End Sub
 
Private Sub Worksheet_Calculate()
        Call FillOnlyOddRows
End Sub

 

[mrkris1982]

CF=Conditional formatting (brain fart)

 

[mrkris1982]

Unfortunately, I do have an issue: I already have 3 conditional formats in use for this worksheet.

 

[mrkris1982]

No clue where to make the chage in the following code, but I would like for it to EXCLUDE rows 1 and 2:

Private Sub FillOnlyOddRows()
    Dim n As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
    For c = 2 To Range("A" & Rows.Count).End(xlUp).Row
    
        If Rows(c).Hidden = False Then
            n = n + 1
            
            If n Mod 2 = 1 Then
                Rows(c).Interior.Color = RGB(153, 204, 255)
            Else
                Rows(c).Interior.Color = vbWhite
            End If
            
        End If
        
    Next c
    
    Application.ScreenUpdating = True
    
End Sub
 
Private Sub Worksheet_Calculate()
        Call FillOnlyOddRows
End Sub

If possible, Id also like it to exclude all colums to the right of J

 

[lenze]

Unfortunately, I do have an issue: I already have 3 conditional formats in use for this worksheet.

You can only have 3 conditions for each cell. You can have many for the worksheet!!
But, if the 3 per cell is a limit, what are the other conditions and how is the data entered. This can be approached with code!!
lenze