multiple Cell formatting based on cell value

[bananatang]

Dear All,

I am finding this forum very useful and looking through posts i have learnt alot about writing macros.

I am stump in finding a workable solution to my problem and I was wondering if someone could help me with the following.

I am trying to write a macro that will perform several formatting options to cells, which contain a specific value. I have attached the macro that I have at the moment, which works fine if you are only going to perform a single formatting option, however I am looking to do various other formatting.

I have a worksheet that will contain various titles. What I would like to do is format that Title cell and 10 columns after it and add a row above it and below it and shade them and to reduce the height of the added rows.

For ease of understanding, I have created a macro called ?colourgroup? which show what I would like to happen to the cell that contains the specific cell values.

I tried using the find method, however runtime errors occur if there are no cell found.

Any help would be appreciated.

[Bob Phillips]

[vba]

Sub colorgroup()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If LCase(.Cells(i, "A").Value) Like "*title*" Then

.Rows(i + 1).Insert
With .Cells(i + 1, "A").Resize(, 9)

.RowHeight = 3
.Interior.ColorIndex = 16
End With

With .Cells(i, "A").Resize(, 9)

.Interior.ColorIndex = 6
End With
ElseIf LCase(.Cells(i + 1, "A").Value) Like "*title*" Then

.Rows(i + 1).Insert
With .Cells(i + 1, "A").Resize(, 9)

.RowHeight = 3
.Interior.ColorIndex = 16
End With
End If
Next i
End With
End Sub
[/vba]

[bananatang]

Hi Xld,

Thank you kindly for your prompt reply.
The code you supplied works great for a single title.

The problem i have is that i would like to use the same format for differant titles. The worksheet will contain at least 5 differant titles that i would like to format simulataneously.

If you could possibly tweak the code to reflect this, that would be great.

Also for payment, do you have paypal, if so please send me your address and payment will be sent.

many thanks
BT

[Bob Phillips]

BT

I catered for multiple lines that contain the word 'title'. What will be in the other rows, how do I identify them?

What payment? This is a free forum, we help solve problems because we do.

[bananatang]

BT

I catered for multiple lines that contain the word 'title'. What will be in the other rows, how do I identify them?

What payment? This is a free forum, we help solve problems because we do.

Hi Xld,

I should have made it clearer in my description. In Column A i have more than one title in differant rows.

for example.

In column A, i have various titles called " Primary school", "Secondary school", "special school" etc

What i would like is for a macro to find all those predefined titles and apply the same format options.

i posted my query on the "help for hire" section, that is why i asked how i make payment.

I hope this makes sense.

BT

[Bob Phillips]

So do we format all rows where it has the word 'school' in the text, with or without any other text, or just those particular values? And if it is the latter, will the text just be those words, or could there be other words in there as well.

I didn't know we had a 'Help for Hire' section.

[bananatang]

So do we format all rows where it has the word 'school' in the text, with or without any other text, or just those particular values? And if it is the latter, will the text just be those words, or could there be other words in there as well.

I didn't know we had a 'Help for Hire' section.

HI Xld,

Once again, thank you for trying to help me out.

The various titles listed in the worksheet will be unique and not all of the titles will have a common word (text value)

For example, the format to be applied to all Names of schools. which again will be listed in Column A.

Look forward to hearing from you.

BT