Automatic colors?

mrkris1982

Active Member
Joined
Apr 16, 2009
Messages
407
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Try:
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(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

Active Member
Joined
Apr 16, 2009
Messages
407
This works pretty good!

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

Thanks
 

mrkris1982

Active Member
Joined
Apr 16, 2009
Messages
407
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.
 

mrkris1982

Active Member
Joined
Apr 16, 2009
Messages
407
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:

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

Active Member
Joined
Apr 16, 2009
Messages
407

ADVERTISEMENT

CF=Conditional formatting (brain fart) :)
 

mrkris1982

Active Member
Joined
Apr 16, 2009
Messages
407
Unfortunately, I do have an issue: I already have 3 conditional formats in use for this worksheet.
 

mrkris1982

Active Member
Joined
Apr 16, 2009
Messages
407
No clue where to make the chage in the following code, but I would like for it to EXCLUDE rows 1 and 2:

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

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

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,719
Messages
5,660,488
Members
418,580
Latest member
IrishDave2137

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top