asked on 9/6/2013

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

Last Comment

Steve

9/6/2013

Steve

9/6/2013

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

Jagwarman

9/6/2013

ASKER

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

9/6/2013

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?

Jagwarman

9/6/2013

ASKER

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?

Jagwarman

9/6/2013

ASKER

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

9/6/2013

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