vba设置单元格字体,以下是设置给定单元格内字体的加粗,单元格填充灰色等,参数是Range对象
Sub font_sty(Rng As Range)Rng.SelectSelection.Font.Bold = TrueWith Selection.Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.ThemeColor = xlThemeColorDark1.TintAndShade = -0.349986266670736.PatternTintAndShade = 0End WithEnd Sub
vba设置单元格边框,以下是设置给定单元格边框,参数是Range对象
Sub sty_sig(Rng As Range)Rng.SelectSelection.Borders(xlDiagonalDown).LineStyle = xlNoneSelection.Borders(xlDiagonalUp).LineStyle = xlNoneWith Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlEdgeTop).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlEdgeRight).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlInsideVertical).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithEnd Sub
对这两个方法(片段)进行调用
我们使用《如何系列(二)》中的例子,最后增加两个call 分别call设置单元格字体格式,和设置单元格边框
Sub sqltest()Dim Spath As StringSpath = ThisWorkbook.Path & "\pbxtest.xlsx"Set adConn = New ADODB.Connection'adConn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';data source=" & Spath & ""Set rs = adConn.Execute("Select 编号,测试环境,测试项目 From [sheet1$a1:j35] ")'sht_name = "sheet3"Set sht = ThisWorkbook.Worksheets(sht_name)'For i = 1 To rs.Fields.Count Step 1sht.Range("A1").Offset(0, i - 1) = rs.Fields(i - 1).Name 'Next i'sht.Range("A2").CopyFromRecordset rssht.Cells.EntireColumn.AutoFitCall sty_sig(Range(Cells(1, 1), Cells(13, 3)))Call font_sty(Range(Cells(1, 1), Cells(1, 3)))End Sub
结果:
之前的: