CODEWITHSUNDEEP
excelvba
Leonard
Leonard

Reputation: 3

changing font and color size based on cell values

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

Upvotes: 0

Views: 546

Answers (1)

adb
adb

Reputation: 258

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

Upvotes: 1

Related Questions