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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,135
Messages
5,509,364
Members
408,730
Latest member
Kayslover

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top