"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 test()
Dim LR As Long, i As Long, icol As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        Select Case .Value
            Case "R": icol = 3
            Case "G": icol = 4
            Case "A": icol = 22
            Case "C": icol = 5
            Case Else: icol = xlAutomatic
        End Select
        .Offset(, 1).Characters(4, 1).Font.ColorIndex = icol
    End With
Next i
End Sub

 

[Chrisb123]

VOG

Thank you very very much! If we ever meet will buy u lots of beer!!!!

Chris

 

[Chrisb123]

VoG

At the risk of appearing like I'm taking the p*^$...!

Could you help again ?!

I need to do a similar thing but this time:

If the value in column B is 1, make the entire cell contents in the corresponding cell in column f Bold and font size 14
If the value in column B is 2, make the entire cell contents in corresponding cell in column F italic and font size 10

again - stopping at the last entry in column B

I WILL be contributing toi this site as I work my way through my VBA books

Thanks for your help!

Chris

 

[VoG]

Try

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

 

[Chrisb123]

VoG

What can I say - thanks very much!!! - this has really helped me out


chris

 

[Chrisb123]

Blimey...please bear with me - there has been a change of plan...

Ok : the vba VoG has provided works brilliantly to format the 8th character of the cell that is offset from the the cell containing the criteria as per below:

Dim LR As Long, i As Long, icol As Long
LR = Range("G" & Rows.Count).End(xlUp).row
For i = 1 To LR
With Range("G" & i)
Select Case .Value
Case "R": icol = 3
Case "G": icol = 4
Case "A": icol = 6
Case "C": icol = 5
Case Else: icol = xlAutomatic
End Select
.Offset(, 1).Characters(8, 1).Font.ColorIndex = icol
End With
Next i


BUT!!!!!

I have now realised that what I have to do is apply the same "case" stuff to ANY CELL TO THE RIGHT OF COLUMN G IN THE SAME ROW AS THE CASE CELL rather than just to the cell offset by 1 column!!!!!!

I know it's a cheek - but any help very very gratefully received!!!!

 

[VoG]

Try

Dim LR As Long, i As Long, icol As Long, LC As Long, j As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LR
    With Range("G" & i)
        Select Case .Value
            Case "R": icol = 3
            Case "G": icol = 4
            Case "A": icol = 6
            Case "C": icol = 5
            Case Else: icol = xlAutomatic
        End Select
        For j = 1 To LC - 7
            .Offset(, j).Characters(8, 1).Font.ColorIndex = icol
        Next j
    End With
Next i

 

[Chrisb123]

VoG

Many thanks mate! I think you may have misread the requirement slightly and i actually managed to change the code meself

Dim LR As Long, i As Long, icol As Long, LC As Long, j As Long

LR = Range("G" & Rows.Count).End(xlUp).row

'note change below - now reads : xltoright not xltoleft
LC = Cells(1, Columns.Count).End(xlToRight).Column

For i = 1 To LR

With Range("G" & i)

Select Case .Value
Case "R": icol = 3
Case "G": icol = 4
Case Else: icol = xlAutomatic
End Select

For j = 1 To LC - 7
.Offset(, j).Characters(2, 1).Font.ColorIndex = icol

Next j

End With

Next i

End Sub

 

[Chrisb123]

I'm sorry! Got one more question...

The Macro:


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

Instead of applying the formats to the cell offset 4 columns, I again need to apply the format to any cell in the row.

For example :

If cell B4 contains "1", then the macro should apply the format: size 14,bold to any cell in row 4, to the right of column B containing an entry.

Sorry for the questions - this is work and I can't tell you how much this is helping me!

cheers
</pre>