Excel auto format cell's font size based on number value?

Question

I want to make cell's number values to be smaller, in font size, with small values i.e. the bigger the cell value is, the bigger is the font size.

I can only find Data Bars in Conditional Formattting.

If you know how to do this, please share.

Solution

You will need a macro in VBA, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wk As Workbook
Set wk = ThisWorkbook
Dim ws As Worksheet
Set ws = ActiveSheet
CRow = Target.Row
CColumn = Target.Column
If CColumn = X Then
    CellValue = Cells(CRow, CColumn)
    Select Case CellValue
        Case Is < 0
            Cells(CRow, CColumn).Font.Size = 8
        Case 1 To 100
            Cells(CRow, CColumn).Font.Size = 10
        Case 101 To 500
            Cells(CRow, CColumn).Font.Size = 12
        Case 501 To 1000
            Cells(CRow, CColumn).Font.Size = 14
        Case 1001 To 5000
            Cells(CRow, CColumn).Font.Size = 18
        Case 5001 To 10000
            Cells(CRow, CColumn).Font.Size = 22
        Case Else
            Cells(CRow, CColumn).Font.Size = 24

    End Select
End If
End Sub

You have to copy the code, go to View -> Macros in Excel, Create a new one (any name is valid) and on the left column double click the worksheet where you want to use it (red flag in the picture) and in the right side, paste the code.

This macro modifies the font size of the cell in the column X of the worksheet, when it detects that the cell has changed. X is the number of the column (A=1, B=2, C=3, ....)

I defined some ranges of values, for example if the value is between 1 and 100 the font size will be 10. You can add, remove or change it according to your needs.

Keep in mind that when you are going to save it, you have to use .xlsm instead of the typical .xlsx. Also, when you open it, Excel always disable macros and shows a security warning asking you if you want to enable macros

One such sample Excel file can be downloaded here.

Comments

[Nam G VU]

The font size box is disabled there @jcbermu

[jcbermu]

You're right. I'll correct my answer

[jcbermu]

I added VBA code that does the formatting that you need

[Nam G VU]

How can I use your VBA function @jcbermu?

[jcbermu]

Copy the code, then open macros in Excel. On the left column double click the worksheet where you want to use it and in the right side, paste it. That's all. It will change the font size of a cell whenever there is a change in it.

[Nam G VU]

How to trigger the code? I pasted and nothing happen.

[jcbermu]

I added an image to clarify the instructions.

[Nam G VU]

It works now and I added one sample in your answer. It's nice. Though, I'm looking for way to format just for a selected column and I'm not good at VBA. May you help me more on that?

[jcbermu]

For just one column, 2 steps: FIRST. Add below the line that says CColumn = Target.Column this line: If CColumn = X Then. X is the number of the column that you want to be formatted, for example for B is 2, for C is 3... and so on. SECOND.Add below End Selectthis line: End If

[Nam G VU]

Please update your answer so that to get accepted. Thank you.

[jcbermu]

Updated for a single column