"Format if" macro help please!!!

[Chrisb123]

Hi all

I have learned loads from the posters on this site - so thanks to all of you for being generous with your knowledge!

I am hoping one of you can help me! I am trying to create a macro to do the following:

In column A will be the following letters R,A,G,C in no particular order. for example:

R
G
R
A
etc etc

The number of entries in column A will vary week by week.

In column B next to each entry in column A will be a cell containing some text, for example: "Jun Red blah blah"

I am trying to create a macro that will go down column A and:

If the entry in column A is "R" then format the 4th character of the cell in column B Red

If the entry in column A is "G" then format the 4th character of the cell in column B Green

If the entry in column A is "A" then format the 4th character of the cell in column B Amber

If the entry in column A is "C" then format the 4th character of the cell in column B Blue

The Macro should go down column A until it gets to the last entry in column A then stop!

I really hope you can help with this - I'm completely stumped with it!!

Many thanks in advance!!!

Chris

 

[VoG]

Try

Sub test2()
Dim LR As Long, i As Long, LC As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LR
    With Range("B" & i)
    Select Case .Value
        Case 1: .Offset(, 1).Resize(, LC - 3).Font.Size = 14: .Offset(, 1).Resize(, LC - 3).Font.Bold = True
        Case 2: .Offset(, 1).Resize(, LC - 3).Font.Size = 10: .Offset(, 1).Resize(, LC - 3).Font.Italic = True
    End Select
    End With
Next i
End Sub

 

[VoG]

For columns M on (as per your PM)

Sub test2()
Dim LR As Long, i As Long, LC As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LR
    With Range("B" & i)
    Select Case .Value
        Case 1: .Offset(, 11).Resize(, LC - 13).Font.Size = 14: .Offset(, 11).Resize(, LC - 13).Font.Bold = True
        Case 2: .Offset(, 11).Resize(, LC - 13).Font.Size = 10: .Offset(, 11).Resize(, LC - 13).Font.Italic = True
    End Select
    End With
Next i
End Sub

 

[Chrisb123]

VoG

Thank you very much - and I hope you are impressed - I fixed it meself whilst waiting for your reply!!!!!!!

That's why this site is brilliant - you learn from it!

Can you recommend a really good start from scratch teach yourself book/course? I have a couple of books I borrowed - but they are too advanced.

Once again - thanks mate

 

[VoG]

Take a look at hiker's suggestions http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5