Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

rows differently colored in excel

Status
Not open for further replies.
wmbb

wmbb

Technical User
Jul 17, 2005
320
NL
Application: Excel 2003
To improve the visual layout I want to give the odd and even rows in the selected area a different color.

I'm trying to this by selecting the area and run the macro below:
Code: 
Sub rowclr()
If ActiveCell.Row Mod 2 Then
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
Else
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
End If
End Sub

Running the macro it gives all the cells the same color.

How can I solve this problem and get the odd and even rows differently colored.
 
Sort by date Sort by votes



Hi,

No need for VBA. Just use Format > Conditional Formatting

Select the range, Format > Conditional Formatting, then enter the Formula Is: referenced to the TOP LEFT CELL of the selected area--a 30 second exersize.



Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
And how to do this in excel 2007 ?
I also have to do this exercise in excel 2007.

I already tried to do this in excel 2007 but the conditional formatting can not be overruled by selecting a fill color when I want to color a single cell in the range another color.

So I would like to do this without conditional formatting.
 
Upvote 0 Downvote


So you want to override the condition. You put that in your CF formula.

But using code...
Code: 
Sub rowclr()
    Dim r As Range
    
    With Selection
        For Each r In Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
            With Intersect(r.EntireRow, .Cells).Interior
                If r.Row Mod 2 Then
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.249977111117893
                    .PatternTintAndShade = 0
                Else
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.349986266670736
                    .PatternTintAndShade = 0
                End If
            End With
        Next
    End With
    
End Sub

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Thnx for your quick response.

Maybe a little bit of the background.
I want a part of the sheet the odd and even rows differently colored (by macro - selecting area and start macro).
Subsequently, in this area I want to color some single cells to visualize the status of a machine.

Say I want to do this in excel 2007...

Where do I have to put the code for the conditional format ?
How do I color the selected cells at once by macro to overrule this CF?
 
Upvote 0 Downvote



You either have to do it ALL as CF, or VBA.

It seems that you have to override requirement, so just do it all in VBA, seems the best choice to me, IMHO.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Thnx for your help.

I will try to do this in another way.
 
Upvote 0 Downvote


Does the VBA not do what you want?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Yes it does when I do not try to color the odd and even rows.
 
Upvote 0 Downvote
You posted
Application: Excel 2003
Click to expand...
but its really 2007, so I had to comment out the
Code: 
'                    .ThemeColor = xlThemeColorDark1
'                    .TintAndShade = -0.249977111117893
'                    .PatternTintAndShade = 0
and use a property for 2003 like .colorindex.

It WORKS!!! Every row of the selection is shaded alternately.

So what's the problem?


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Some users are using excel 2003 for this design but there are also users who are going to use this in excel 2007.
Thats the reason why I try both versions...

I've restyled the sheet and use the static range to color the odd and even rows manualy.
Subsequently users can color the selected cells by macro.
Hereby something happens that I can not understand but I will put this question in a new thread.

For now Thanks for so far.
 
Upvote 0 Downvote
Maybe one last question...

... how do I get the code below working for all selected cells (PE A11, B13, C9 ...)?

Code: 
Sub fillcell()
If Selection.Value = 0 Then
        Selection.Value = 4
        Selection.Font.Color = 16777215
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16711680
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
ElseIf Selection.Value = 4 Then
        Selection.Value = 8
        Selection.Font.Color = 1
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16711680
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Else
    If Selection.Row Mod 2 Then
        Selection.ClearContents
        With Selection.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With
    Else
        Selection.ClearContents
        With Selection.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.349986266670736
            .PatternTintAndShade = 0
        End With
    End If
End If

End Sub

Now all the selected cells are treated as the first cell in the selection.
 
Upvote 0 Downvote



HUH???

Why did you ignore my code? It answers your question!!!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Sorry, it isn't that I don't want to use your code but it doesn't work for me...
I've tried the code below but like I wrote before all the selected cells are treated as the first cell in the selection !?

Am I to stupid for this??

Code: 
Sub fillcell()
Dim r As Range
    
With Selection
     For Each r In Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
         With Intersect(r.EntireRow, .Cells).Interior

            If Selection.Value = 0 Then
                Selection.Value = 4
                Selection.Font.Color = 16777215
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 16711680
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            ElseIf Selection.Value = 4 Then
                Selection.Value = 8
                Selection.Font.Color = 1
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 16711680
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            Else
                If Selection.Row Mod 2 Then
                    Selection.ClearContents
                    With Selection.Font
                        .ColorIndex = xlAutomatic
                        .TintAndShade = 0
                    End With
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                Else
                Selection.ClearContents
                With Selection.Font
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                End With
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.349986266670736
                    .PatternTintAndShade = 0
                End With
                End If
            End If
         End With
     Next
End With
End Sub
 
Upvote 0 Downvote



How come you have Selection INSIDE THE LOOP?????

That won't work!!! That's NOT my code!!!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
OKE I can see the finish now...

I changed the code into
Code: 
Sub fillcell()
Dim r As Range
    
With Selection
     For Each r In Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
         With Intersect(r.EntireRow, .Cells)

            If r.Value = 0 Then
                r.Value = 4
                r.Font.Color = 16777215
                With r.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 16711680
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            ElseIf r.Value = 4 Then
                r.Value = 8
                r.Font.Color = 1
                With r.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 16711680
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            Else
                If r.Row Mod 2 Then
                    r.ClearContents
                    With r.Font
                        .ColorIndex = xlAutomatic
                        .TintAndShade = 0
                    End With
                    With r.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                Else
                r.ClearContents
                With r.Font
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                End With
                With r.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.349986266670736
                    .PatternTintAndShade = 0
                End With
                End If
            End If
         End With
     Next
End With
End Sub

Now the macro runs for every first cell in each row.
I think the solution is in the lines below but I can't solve it because I don't know the code Intersect.
Code: 
With Selection
     For Each r In Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
         With Intersect(r.EntireRow, .Cells)

How do I get the code work for all individual selected cells for example (A11, A12, A13, B13, C9, C12, C16)
 
Upvote 0 Downvote
I've found it !!!

I've replaced the code above with
Code: 
For Each Cell In Selection

How simple can it be...


Thanks everyone for your help.
I've learned something again !
 
Upvote 0 Downvote



Suit yourself.

Your method tests each and every cell in the selection. Maybe that's what you wanted, but that’s not what I understood you asked for.

You wanted each ROW within the selection shaded. That's what my code does, but you apparently never tried it. Your code is slower, as it must assign the shade of each cell in a row individually. Mine does the row at one fell swoop.

Yours will indeed work, but it is extremely disappointing to me that someone would so blatantly disregard good suggestions as you seem to have done.


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I'm sorry if you feel that way.
Your suggestion was one of my first questions and I did use your code in the first place.
It also did what I wanted but that was just a part of what I wanted to do.
Later on I thought there was a better way to solve my question and combine two actions (custom fill cells and reset them).
That was what I've tried to say in my reaction on 27 Jul 09 2:59.
I'm sorry if you missed that part because I appreciate your help a lot.
I've learned a lot from your suggestions, now and in my previous threads.

[red]So don't be angry.
I will try to be more clearly the next time, I promise.[/red]


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
444
remeng
remeng
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
466
sal21
sal21
Andrzejek
  • Locked
  • Question
Hyperlinks in Word cut at #
Replies
2
Views
229
Andrzejek
Andrzejek
huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
660
huiettcm
huiettcm

Part and Inventory Search

Sponsor

Back
Top