changing font and color size based on cell values

Question

This is very different to what I have found in searches using this question.

I want to be able to change font size and color (RGB) based on cell values for a range. This would allow users to customize a form (size and colour of headings, general text, etc.)

For font size, this, below, works perfectly, but it doesn't work for font colour unless I specify the colour in the code.

Cell B1 contains the font size, 12, and cell B2 contains 255, 255, 0

Sub fontsizecolor ()

Dim rgb2 As String
rgb2 = ThisWorkbook.Worksheets("sheet1").Range("B2").Text

With Worksheets("Sheet1").Range("A1:A10")
 .Font.Size = Range("B1")
        '.Font.Color = RGB(255, 255, 0)  **<---- this works**
 
 .Font.Color = RGB(rgb2)
 
 
End With

End Sub

RGB expects 3 numeric arguments. You are passing it one string. You need to either parse the string into 3 arguments, perhaps using Split, or put the 3 numeric values in 3 different cells. — BigBen

adb adb · Accepted Answer · 2020-11-09T21:40:25

As BigBen mentions you need to provide 3 arguments to RGB and the Split function can achieve that for you.

In the below example we take the text from B2, split it and store the elements into an array. Then we simply feed those elements into RGB:

Sub fontsizecolor()

    Dim rgb2, rgbArray() As String
    
    rgb2 = ThisWorkbook.Worksheets("sheet1").Range("B2").Text
    rgbArray = Split(rgb2, ",") ' split string on ","
    
    With Worksheets("Sheet1").Range("A1:A10")
        .Font.Size = Range("B1")
        .Font.Color = RGB(rgbArray(0), rgbArray(1), rgbArray(2)) ' feed array elements as arguments
    End With

End Sub

changing font and color size based on cell values

1 Answers