Shade rows by value in one column

[COURTTROOPER]

I need help with my code. I am trying to have every other line shaded except when column 2 has “X” in it.
If Column “B” has an X in it the row should have the yellow fill then start the every other row gray shading until column “B” has another “X” and that row is to be filled yellow.
I get a “Type Mismatch error 13” with “If Range("B" & rng) = "X" Then” highlighted. I tried using “If Range("B" & rng).Value = "X" Then” it gives me the same error.
I have uploaded a sample workbook with sheet1 that I have the code on that errors, and sheet2 with a sample of what I am trying to do.
All Help is greatly appreciated!

Here is my code:

Please Login or Register  to view this content.

Thanks again for any help!

[JLGWhiz]

Try this and see if it does what you want.

Please Login or Register  to view this content.

[COURTTROOPER]

I tried your code line and it will run but it doesn't color it yellow in rows with "X" , it just shades every other line in gray. I tried several variations but still can't get it to yellow "X" rows. I did get it to yellow rows but it was the row after the "X". do you have any other ideas? I do appreciate your help!

[Marc L]

As I do not understand why you use this event but anyway try this :

Sub Demo1()
        Dim R&
    With Me.UsedRange.Rows
           .Interior.ColorIndex = xlNone
        For R = 2 To .Count
            With .Item(R)
                If .Cells(2).Value = "X" Then .Interior.Color = vbYellow Else _
                If R And 1 Then .Interior.ColorIndex = 15
            End With
        Next
    End With
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[COURTTROOPER]

Sorry I forgot to give you my latest version of the code. Also I need the rows that are yellow to stay yellow.

Please Login or Register  to view this content.

This code is shading the row but when you move to the next row only that row is shaded.

[COURTTROOPER]

Marc L that works perfectly! I am very new to VBA and trying to learn as I go (I also have a few dummy books). Could you please comment what is happing in each row of your code? I have never seen the "Dim R&" before what does the "&" do or mean?
To answer your question about why I am using an event is because I am using it on a phone list that changes and has more entries added to it all the time. If you have a better way to accomplish this I am open to all help and ideas.
Thanks again for both yours and JLGWhiz help.

[COURTTROOPER]

Marc L Thanks for all your help but I have found that the code fills the rows for the entire width of the sheet and I need it to fill only my active columns which are from columns "A" through "J". How can I have it fill only the width of my form columns "A" through "J"?
I know how to set a range but don't know how to make it work with your code. All help is greatly appreciated!

[Marc L]

A to J ? As your original attachment has only A to C …

The common case when explanation & attachment do not reflect the real need & workbook !
Just add a Columns statement for the Interior property …

[JLGWhiz]

Using MarcL code

Please Login or Register  to view this content.

Should fill A:J on the applicable rows.

[COURTTROOPER]

Thank you both for your help and code. In my first post I used 3 columns "A" through "C" as an example but the real sheet uses columns "A" through "J" and that is what spurred the second question about how to limit it on the real sheet. As I said I am new to VBA so my methods and questions may seen simple to those with lots of experience so I apologize if I ask dumb questions. I am having a hard time grasping the lingo of "interior properties, object, etc." Thanks again for all the help and patience. I will be marking this one solved.

[JLGWhiz]

The dumbest questions are those which are not asked. If you need to know something, ask. Just don't keep asking the same question over and over. We have all gone through the learning stage and I, for one, still ask questions when I don't understand something.
regards, JLG