Colour code range of cells

I have used the below code previously but need to make a slight amendment in my current project.

In this project I need to colour cells B through T whereas the code below only colours the cell containing the word:

Sub CondtionalFormats()
   
    Dim vCells As Range

    For Each vCells In Range("A1:A32768").Cells
        If Left(vCells.Text, 4) = "Book" Then
            vCells.Interior.ColorIndex = 43
            vCells.Borders.LineStyle = xlContinuous
            vCells.Borders.Weight = xlThin
        End If
    Next vCells
     
End Sub

Would appreciate some help.
Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
You would like to colour words starting wit B to T?
Or columns B to T words staring Book?
0
JagwarmanAuthor Commented:
Rows, where the word in column 'B' start with Book [ but I may want to change the word from Book to some other word later] and I need to colour from B to T

Thanks
0
SteveCommented:
ok...

Sub CondtionalFormats()    
    Dim vCells As Range

    For Each vCells In Range("B1:T32768").Cells
        If Left(vCells.Text, 4) = "Book" Then
            vCells.Interior.ColorIndex = 43
            vCells.Borders.LineStyle = xlContinuous
            vCells.Borders.Weight = xlThin
        End If
    Next vCells
     
End Sub 

Open in new window


This will do B to T but between rows 1 to 32768
Can set the rows to a number or to let excel work it out, but up to you

You can get conditional formatting to do this for you, rather than VBA.
Would you like a demo of the Conditional Formatting method?
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

JagwarmanAuthor Commented:
I could do Conditional formatting but there are numerous 'words' where I need to change row colours so prefer to use VBA.

Not sure why but as I mentioned originally the code only colours the cell[s] that contain the word it does not colour the whole row B through T.

Is it not possible?
0
JagwarmanAuthor Commented:
Just thought ?

Only the one cell [i.e. the one in column 'B'] will have the word 'Book' but I want all of the cells in the row B through T to be highlighted.
0
SteveCommented:
Try the following...
looking for cells in B1 to the last full cell in B which start "book"
then those found are formatted B to T

Sub CondtionalFormats()
    Dim vCells As Range

    For Each vCells In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Cells
        If Left(vCells.Text, 4) = "Book" Then
            With Range(Cells(vCells.Row, "B"), Cells(vCells.Row, "T"))
                .Interior.ColorIndex = 43
                .Borders.LineStyle = xlContinuous
                .Borders.Weight = xlThin
            End With
        End If
    Next vCells
     
End Sub

Open in new window


Is the word starting book actually in A?
If yes try:

Sub CondtionalFormats()
    Dim vCells As Range

    For Each vCells In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
        If Left(vCells.Text, 4) = "Book" Then
            With Range(Cells(vCells.Row, "B"), Cells(vCells.Row, "T"))
                .Interior.ColorIndex = 43
                .Borders.LineStyle = xlContinuous
                .Borders.Weight = xlThin
            End With
        End If
    Next vCells
     
End Sub

Open in new window

0
SteveCommented:
OK.. this is a bit ahead of where the code above gets to:

Sub CondtionalFormats()
    Dim vCells As Range

    Dim myWords
    Dim strWords As String
    
    myWords = Split("Book Word Other")
    Dim x As Long
    
    For x = 0 To UBound(myWords)
        For Each vCells In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
            If vCells.Text Like myWords(x) & "*" Then
                With Range(Cells(vCells.Row, "B"), Cells(vCells.Row, "T"))
                    .Interior.ColorIndex = 43
                    .Borders.LineStyle = xlContinuous
                    .Borders.Weight = xlThin
                End With
            End If
        Next vCells
    Next x
End Sub

Open in new window


If you change "Book Word Other" to be the words you are looking for (seperated by spaces) then this will work for multiple words.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Bingo Thanks. Have a great weekend
0
JagwarmanAuthor Commented:
Exactly what I was looking for.
0
SteveCommented:
You have a great weekend too, enjoy :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.