Avatar of Jagwarman
Jagwarman asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
Steve

8/22/2022 - Mon
Steve

You would like to colour words starting wit B to T?
Or columns B to T words staring Book?
ASKER
Jagwarman

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
Steve

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Jagwarman

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?
ASKER
Jagwarman

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.
Steve

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Steve

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Jagwarman

Bingo Thanks. Have a great weekend
ASKER
Jagwarman

Exactly what I was looking for.
Steve

You have a great weekend too, enjoy :)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes