Converstation Formatting

[routeski]

Hello

I am not sure if Excel is the best package to use for this, but I think so...
I have an iPhone and have downloaded all my SMS messages into a .TXT file and looks like this:

From Ben Skart at Nov 22, 2008 23:15 :
You got a spare ticket for oasis? Mate of mine would cut a nut off for a ticket.
Send To Ben Skart at Nov 22, 2008 23:41 :
Sorry I don't have this number? But yes I think I may have a spare one!
From Ben Skart at Nov 22, 2008 23:42 :
Its ben, Claire's boyfriend.
Send To Ben Skart at Nov 22, 2008 23:44 :
Ah right! Sorry man, didn't save your number. Yeah I might have one but probably won't know until the tickets get sent out.

What I want to do is have it on my screen like an iPhone conversation: Messages I've sent on Column B in Green and Received in Grey on Col A.

However there are 1000s of these messages and they don't always take up a single line and something there is a carriage return and then more message. Sometimes he replies and then sends another message before I have so its not SENT/RECEIVED/SENT/RECEIVED kind of strict order.

I hope this makes sense! I'm thinking conditional formatting will help
THanks
Routeski

 

[SteveO59L]

Do the messages always start with From or Send ?

 

[routeski]

Yes, always start with the word From and Send but obviously the time and date changes after it

 

[routeski]

I have had another idea, which may simplify things in peoples minds.

IF a cell begins with "From" then make the row green, and all the rows below it green UNTIL a cell begins with "Sent" in which case make it red, and all the rows below it red UNTIL a cell begins with "From" then make the row green, and all the rows below it green.........and repeat ad nausem!

 

[SteveO59L]

Sorry, I had forgotten about this.

Dim c As Range, rng
Columns("a:a").Select
lastrow = Cells(65536, Selection.Column).End(xlUp).Row

Set rng = Range("a1:a" & lastrow)


For Each c In rng
If Left(c, 4) = "From" Then
c.EntireRow.Interior.ColorIndex = 35
End If
Next c
For Each c In rng
If Left(c, 4) = "Send" Then
c.EntireRow.Interior.ColorIndex = 8
End If
Next c
Set rng = Range("a2:a" & lastrow)
For Each c In rng
If c.Offset(-1, 0).Interior.ColorIndex = 35 And c.Interior.ColorIndex = xlNone Then
c.EntireRow.Interior.ColorIndex = 35
End If
Next c
For Each c In rng
If c.Offset(-1, 0).Interior.ColorIndex = 8 And c.Interior.ColorIndex = xlNone Then
c.EntireRow.Interior.ColorIndex = 8
End If
Next c
For Each c In rng
If c.Offset(-1, 0).Interior.ColorIndex = 8 And c.Interior.ColorIndex = xlNone Then
c.EntireRow.Interior.ColorIndex = 8
End If
Next c

 

[routeski]

Superb. Thanks very much.

Next step would be to put a border round each conversation and shift the "sent" item to another column a la iPhone?