Excel根据单元格背景色批量设置格式的宏
循环当前workbook下的所有sheet并对其中的有效单元格进行格式的重置：
1 Sub SetFont() 2 3 Dim my As Worksheet 4 For Each my In Worksheets 5 my.Activate 6 For Each c In ActiveCell.CurrentRegion.Cells 7 If c.Interior.ColorIndex = 15 Then 8 c.Font.Name = "黑体" 9 c.Font.Size = 12 10 c.Font.Strikethrough = False 11 c.Font.Superscript = False 12 c.Font.Subscript = False 13 c.Font.OutlineFont = False 14 c.Font.Shadow = False 15 c.Font.Underline = xlUnderlineStyleNone 16 c.Font.ColorIndex = xlAutomatic 17 c.Font.TintAndShade = 0 18 c.Font.ThemeFont = xlThemeFontNone 19 c.HorizontalAlignment = xlCenter 20 c.VerticalAlignment = xlCenter 21 c.WrapText = True 22 c.Orientation = 0 23 c.AddIndent = False 24 c.IndentLevel = 0 25 c.ShrinkToFit = False 26 c.ReadingOrder = xlContext 27 ' c.MergeCells = False 28 c.Font.Bold = False 29 ' c.Value = c.Interior.ColorIndex 30 Else 31 c.Font.Name = "宋体" 32 c.Font.Size = 12 33 c.Font.Strikethrough = False 34 c.Font.Superscript = False 35 c.Font.Subscript = False 36 c.Font.OutlineFont = False 37 c.Font.Shadow = False 38 c.Font.Underline = xlUnderlineStyleNone 39 c.Font.ColorIndex = xlAutomatic 40 c.Font.TintAndShade = 0 41 c.Font.ThemeFont = xlThemeFontNone 42 c.HorizontalAlignment = xlLeft 43 c.VerticalAlignment = xlCenter 44 c.WrapText = True 45 c.Orientation = 0 46 c.AddIndent = False 47 c.IndentLevel = 0 48 c.ShrinkToFit = False 49 c.ReadingOrder = xlContext 50 ' c.MergeCells = False 51 c.Font.Bold = False 52 ' c.Value = c.Interior.ColorIndex 53 End If 54 Next 55 56 ActiveCell.CurrentRegion.Select 57 Selection.Borders(xlDiagonalDown).LineStyle = xlNone 58 Selection.Borders(xlDiagonalUp).LineStyle = xlNone 59 With Selection.Borders(xlEdgeLeft) 60 .LineStyle = xlContinuous 61 .ColorIndex = 0 62 .TintAndShade = 0 63 .Weight = xlThin 64 End With 65 With Selection.Borders(xlEdgeTop) 66 .LineStyle = xlContinuous 67 .ColorIndex = 0 68 .TintAndShade = 0 69 .Weight = xlThin 70 End With 71 With Selection.Borders(xlEdgeBottom) 72 .LineStyle = xlContinuous 73 .ColorIndex = 0 74 .TintAndShade = 0 75 .Weight = xlThin 76 End With 77 With Selection.Borders(xlEdgeRight) 78 .LineStyle = xlContinuous 79 .ColorIndex = 0 80 .TintAndShade = 0 81 .Weight = xlThin 82 End With 83 With Selection.Borders(xlInsideVertical) 84 .LineStyle = xlContinuous 85 .ColorIndex = 0 86 .TintAndShade = 0 87 .Weight = xlThin 88 End With 89 With Selection.Borders(xlInsideHorizontal) 90 .LineStyle = xlContinuous 91 .ColorIndex = 0 92 .TintAndShade = 0 93 .Weight = xlThin 94 End With 95 96 Range("A1:K1").Select 97 With Selection 98 .HorizontalAlignment = xlGeneral 99 .VerticalAlignment = xlCenter 100 .WrapText = True 101 .Orientation = 0 102 .AddIndent = False 103 .IndentLevel = 0 104 .ShrinkToFit = False 105 .ReadingOrder = xlContext 106 .MergeCells = True 107 End With 108 Selection.UnMerge 109 With Selection 110 .HorizontalAlignment = xlCenter 111 .VerticalAlignment = xlCenter 112 .WrapText = True 113 .Orientation = 0 114 .AddIndent = False 115 .IndentLevel = 0 116 .ShrinkToFit = False 117 .ReadingOrder = xlContext 118 .MergeCells = False 119 End With 120 Selection.Merge 121 With Selection 122 .HorizontalAlignment = xlRight 123 .VerticalAlignment = xlCenter 124 .WrapText = True 125 .Orientation = 0 126 .AddIndent = False 127 .IndentLevel = 0 128 .ShrinkToFit = False 129 .ReadingOrder = xlContext 130 .MergeCells = True 131 End With 132 With Selection.Font 133 .Name = "黑体" 134 .Size = 12 135 .Strikethrough = False 136 .Superscript = False 137 .Subscript = False 138 .OutlineFont = False 139 .Shadow = False 140 .Underline = xlUnderlineStyleNone 141 .ColorIndex = xlAutomatic 142 .TintAndShade = 0 143 .ThemeFont = xlThemeFontNone 144 End With 145 Range("A2:K2").Select 146 With Selection 147 .HorizontalAlignment = xlCenter 148 .VerticalAlignment = xlCenter 149 .WrapText = True 150 .Orientation = 0 151 .AddIndent = False 152 .IndentLevel = 0 153 .ShrinkToFit = False 154 .ReadingOrder = xlContext 155 .MergeCells = True 156 End With 157 With Selection.Font 158 .Name = "黑体" 159 .Size = 16 160 .Strikethrough = False 161 .Superscript = False 162 .Subscript = False 163 .OutlineFont = False 164 .Shadow = False 165 .Underline = xlUnderlineStyleNone 166 .ColorIndex = xlAutomatic 167 .TintAndShade = 0 168 .ThemeFont = xlThemeFontNone 169 End With 170 Rows("1:1").Select 171 Selection.Cut 172 Rows("3:3").Select 173 Selection.Insert Shift:=xlDown 174 175 Range("A1:K2").Select 176 Selection.Borders(xlDiagonalDown).LineStyle = xlNone 177 Selection.Borders(xlDiagonalUp).LineStyle = xlNone 178 Selection.Borders(xlEdgeLeft).LineStyle = xlNone 179 Selection.Borders(xlEdgeTop).LineStyle = xlNone 180 Selection.Borders(xlEdgeBottom).LineStyle = xlNone 181 Selection.Borders(xlEdgeRight).LineStyle = xlNone 182 Selection.Borders(xlInsideVertical).LineStyle = xlNone 183 Selection.Borders(xlInsideHorizontal).LineStyle = xlNone 184 Selection.Borders(xlDiagonalDown).LineStyle = xlNone 185 Selection.Borders(xlDiagonalUp).LineStyle = xlNone 186 Selection.Borders(xlEdgeLeft).LineStyle = xlNone 187 Selection.Borders(xlEdgeTop).LineStyle = xlNone 188 With Selection.Borders(xlEdgeBottom) 189 .LineStyle = xlContinuous 190 .ColorIndex = 0 191 .TintAndShade = 0 192 .Weight = xlThin 193 End With 194 Selection.Borders(xlEdgeRight).LineStyle = xlNone 195 Selection.Borders(xlInsideVertical).LineStyle = xlNone 196 Selection.Borders(xlInsideHorizontal).LineStyle = xlNone 197 198 Application.PrintCommunication = False 199 With ActiveSheet.PageSetup 200 .LeftHeader = "" 201 .CenterHeader = "" 202 .RightHeader = "" 203 .LeftFooter = "" 204 .CenterFooter = "" 205 .RightFooter = "" 206 .LeftMargin = Application.InchesToPoints(0.78740157480315) 207 .RightMargin = Application.InchesToPoints(0.78740157480315) 208 .TopMargin = Application.InchesToPoints(0.78740157480315) 209 .BottomMargin = Application.InchesToPoints(0.78740157480315) 210 .HeaderMargin = Application.InchesToPoints(0.511811023622047) 211 .FooterMargin = Application.InchesToPoints(0.78740157480315) 212 .PrintHeadings = False 213 .PrintGridlines = False 214 .PrintComments = xlPrintNoComments 215 .PrintQuality = 600 216 .CenterHorizontally = False 217 .CenterVertically = False 218 .Orientation = xlLandscape 219 .Draft = False 220 .PaperSize = xlPaperA4 221 .FirstPageNumber = xlAutomatic 222 .Order = xlDownThenOver 223 .BlackAndWhite = False 224 .Zoom = 100 225 .PrintErrors = xlPrintErrorsDisplayed 226 .OddAndEvenPagesHeaderFooter = False 227 .DifferentFirstPageHeaderFooter = False 228 .ScaleWithDocHeaderFooter = True 229 .AlignMarginsHeaderFooter = False 230 .EvenPage.LeftHeader.Text = "" 231 .EvenPage.CenterHeader.Text = "" 232 .EvenPage.RightHeader.Text = "" 233 .EvenPage.LeftFooter.Text = "" 234 .EvenPage.CenterFooter.Text = "" 235 .EvenPage.RightFooter.Text = "" 236 .FirstPage.LeftHeader.Text = "" 237 .FirstPage.CenterHeader.Text = "" 238 .FirstPage.RightHeader.Text = "" 239 .FirstPage.LeftFooter.Text = "" 240 .FirstPage.CenterFooter.Text = "" 241 .FirstPage.RightFooter.Text = "" 242 End With 243 Application.PrintCommunication = True 244 Next 245 246 End Sub
