> 自然

excel按数值范围统计(excel根据数字范围赋值)

【实例03-按数值范围标记行】【实例04-按数值范围标记列】

实例03-按数值范围标记行 excel按数值范围统计(excel根据数字范围赋值)

Private Sub CommandButton处理_Click()

&34;操作界面&34;C&34;&34;C&34;&34;C&34;&34;C&34;&34;C&34;&34;C&34;&34;D&34;&34;参数不能为空&39;定义变量

Dim wbname As String

Dim shname As String

wbname = Trim(.Cells(2, &34;).Value)

shname = Trim(.Cells(6, &34;).Value)

Dim matchcolumn As Long

Dim startnum As Long

Dim stopnum As Long

matchcolumn = Trim(.Cells(10, &34;).Value)

startnum = Trim(.Cells(14, &34;).Value)

stopnum = Trim(.Cells(14, &34;).Value)

Dim backcolornum As Integer

Dim fontcolornum As Integer

backcolornum = Trim(.Cells(18, &34;).Value)

fontcolornum = Trim(.Cells(22, &34;).Value)

End With

&39;获得数据区域最大行号

Dim rmax As Long

Dim cmax As Long

rmax = .UsedRange.Cells(.UsedRange.Count).Row

&39;标记单元格

&39; .Cells(i, matchcolumn).Font.ColorIndex = fontcolornum

&34;处理完成&39;判断工作簿名，工作表名不为空

With ThisWorkbook.Worksheets(&34;)

If Trim(.Cells(2, &34;).Value) = &34; Or Trim(.Cells(6, &34;).Value) = &34; Or Trim(.Cells(10, &34;).Value) = &34; _

Or Trim(.Cells(18, &34;).Value) = &34; Or Trim(.Cells(22, &34;).Value) = &34; Or Trim(.Cells(14, &34;).Value) = &34; Or Trim(.Cells(14, &34;).Value) = &34; Then

MsgBox &34;

Exit Sub

End If

On Error GoTo 处理出错

&34;C&34;C&34;C&34;C&34;D&34;C&34;C&39;处理表格

With Workbooks(wbname).Worksheets(shname)

&39;循环判断（反向）

Dim i

For i = 1 To cmax

If IsNumeric(.Cells(matchrow, i)) = True Then

If CDbl(.Cells(matchrow, i)) >= startnum And CDbl(.Cells(matchrow, i)) <= stopnum Then

&39;标记整列

&39; .Columns(i).Font.ColorIndex = fontcolornum

End If

Next i

End With

MsgBox &34;

Workbooks(wbname).Activate

ActiveWindow.WindowState = xlMaximized

Workbooks(wbname).Worksheets(shname).Activate

Workbooks(wbname).Worksheets(shname).Cells(1, 1).Select

Exit Sub

处理出错:

MsgBox Err.Description

End Sub