repeat vba for other columns

[ovk]

I'm a vba rookie and have working vba code to check the value of each cell in range b2:b4 and use each of these values to format size of text in each cell in range d2:d4.
ex: if b2 = 1, d2 is formatted 12; if b2 = 5, d2 is formatted 16

My questions: how can I efficiently repeat this for adjacent columns?
(for example with a loop or other method)

Here's what i have so far:

Sub Changefont()
For Each cell In Range("b2:b4")
If Range("b" & cell.Row).Value = 1 Then
cell.Offset(0, 2).Font.Size = 12
ElseIf Range("b" & cell.Row).Value = 5 Then
cell.Offset(0, 2).Font.Size = 18
ElseIf Range("b" & cell.Row).Value = 9 Then
cell.Offset(0, 2).Font.Size = 24
Else: cell.Offset(0, 2).Font.Size = 8
End If
Next cell
End Sub​

 

[Weaver]

Is it that you want the text size to apply to more than 1 column?

first off, I'd avoid using reserved words or similar for variable names
Next, I'd use the select..case construct
Then I'd use resize to affect a larger range of cells,

Sub Changefont()
    Dim c, fs
    For Each c In Range("b2:b4").Cells
        Select Case c.Value
            Case 1
                fs = 12
            Case 5
                fs = 18
            Case 9
                fs = 24
            Case Else
                fs = 8
        End Select
        c.Offset(0, 2).Resize(, 10).Font.Size = fs
    Next c
End Sub

Note, the (,10) says "don't change the rows, but make range 10 cells wide"
HTH

 

[ovk]

Thanks for pointing me toward "cases".

To clarify my original question:
I want the value of B2 to impact the font in D2, the value of B3 to impact the value of D3, etc

AND

I want the value of C2 to impact the font in E2, the value of C3 to impact the value of E3, etc

something like:

A B C D E
1 bob sue bob sue
2 effort 1 9 E E
3 value 5 1 V V
4 charm 9 5 C C

 

[ovk]

Thanks again Weaver!
I got the code doing what I want by changing
"For Each c In Range("b2:b4").Cells"
to " For Each c In Range("b2:c4").Cells"

Now I would like to change the color of the font based on the cell values.

Any suggestions?

Thanks in advance,

OvK

 

[ovk]

Aha! I think I have a full solution!
I couldn't have done it without your help!

Sub Changefont2()
    Dim c, fs
    For Each c In Range("b2:c4").Cells
        Select Case c.Value
            Case 1, 2
                fs = 8
                fcolor = 4
            Case 3, 4, 5
                fs = 12
                fcolor = 6
            Case 6, 7, 8
                fs = 16
                fcolor = 46
            Case 9, 10
                fs = 20
                fcolor = 3
        End Select
        c.Offset(0, 2).Resize(, 10).Font.Size = fs
        c.Offset(0, 2).Font.ColorIndex = fcolor
    Next c
End Sub

 

[Weaver]

Sorry, I couldn't get back to you sooner, but it looks like you did okay by yourself.

Thanks for the feedback.