Cell value specific font size change

[Guest]

Would like to format font size in one column, according to a value in
another, e.g. if I have the data

A B
1 12 X
2 6 Y
3 16 X
4 9 Y
...

I would like the font size of the cells in column B to be the relevant
number in column A

(Going to use wingding arrows in B, but they won't display here)

Thanks,
Geoff.

 

[Guest]

For a single cell:

Sub size_it()
Range("B1").Font.Size = Range("A1").Value
End Sub


You can setup a loop to cover the full columns.

 

[Ron de Bruin]

Hi Geoff

You can try this event in the sheet module
If you fill in a number in A the font change in B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If IsNumeric(Target) Then
Target.Offset(0, 1).Font.Size = Target.Value
End If
End If
End Sub

 

[Norman Jones]

Hi Geoff,

Try:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set SH = Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("B1:B10") '<<==== CHANGE

For Each rCell In rng.Cells
With rCell
.Font.Size = .Offset(0, -1).Font.Size
End With
Next rCell

End Sub
'<<=============

 

[Guest]

Sorry for being dim, but it's the loop I need help with!

 

[Guest]

Hi Geoff:

Sub size_it()
Dim i As Integer
For i = 1 To 10
Cells(i, 2).Font.Size = Cells(i, 1).Value
Next
End Sub

The loop is set up to run over 10 items. This is an example of the
advantage of using CELLS() over RANGE() if you want to process blocks of
cells by row x column.

 

[Norman Jones]

Hi Geoff.

Reading more carefully, change:

.Font.Size = .Offset(0, -1).Font.Size
to

.Font.Size = .Offset(0, -1).Value

If, however, you wish the font size to respond dynamically to changes in
column A values, try instead Ron de Bruin's suggestion.

 

[Guest]

Thanks to all of you for your suggestions. Not only is my request answered,
I've learnt about seven other things!