VBA to change font size and add borders to a range based on selection from a comboBox

[j3rdy]

Hello everyone at Mr excel. Please forgive me if I sound a bit naive in the description of my problem. I'm neither a beginner nor an expert. I know just enough to get me in trouble. My particular problem is I have a spreadsheet that populates it cells from seven other sheets in my workbook based on the selection from a comboBox. A bit of background: The comboBox is linked to cell M5 and has seven options to choose from. Six of the options and sheets it pulls its data from are identical in structure and format. The range ("A42:D42") is a subrow from descriptive headings in Range ("A28:D28"). The seventh sheet "SundayAM", is different. Range ("A42:D42") becomes a descriptive heading with 10 subrows beneath it. The only problem I have is with the formatting. Conditional formatting is a bit limited in what you can do. You can't change font size and can only use the xlThin for the border. What i need to happen: If the linked cell (M5) = "Sunday AM", i need the following range ("A42:D42") to change its font size from 10 to 11, the background color to become (white, background 1, darker 35%) and have medium borders on the top, left and right of the range ("A42:D42"). I'm using Excel 2010, but this need to be backwards compatible with 2003. This is my first post to a message board so I hope the description doesn't sound to confusing. Many Thanks for any help received.

 

[j3rdy]

I got to thinking, since I'm relying on the comboBox to populate the cell("M5"), I could use the comboBox_change() event to initiate the changes I needed. I came up with the following code, using a macro to copy the code. If anyone can trim the code or make it more streamline, please post. I used traditional conditional formatting to initiate the fill color I need in the cell.

 Private Sub ComboBox1_Change()If Cells.Range("$M$5") = "Sunday AM" Then
    Cells.Range("A42:E42").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Cells.Range("$M$5").Select
    
ElseIf Cells.Range("$M$5") <> "Sunday AM" Then
    Cells.Range("A42:E42").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
   Cells.Range("$M$5").Select
End If
End Sub

 

[jsotola]

what about using conditional formatting ?

 

[j3rdy]

what about using conditional formatting ?

Thank you jsotola. I initially thought of using conditional formatting, and it did the basic formatting that i needed. Indeed, I used conditional formatting to change the background color. However, because the range became a descriptive heading for ten rows below it, I needed to change the font size +1 and wrap the selected range and the ten rows below it in a medium border, conditional formatting couldn't do what I wanted. Also, because six other sheets populated the cells depending on the selection of the comboBox, I needed the changes to revert to the original settings (font size -1 and xlThin borders top and bottom and xlmedium borders left and right of the range when "Sunday AM" wasn't selected. A Bit off subject: The code "("cells.Range "("$M$5")". Select at the end of both codes was used because it is hidden behind the comboBox. If any other cell was selected on the sheet it would also adapt the changed format the code was producing and I couldn't find a way to prevent that. I don't know how to upload the actual workbook to show what I mean. If anyone knows a site I can upload it to so I can link to it on this site please post.