www.5129.net > EXCEL VBA同行计算填充颜色的单元格个数

EXCEL VBA同行计算填充颜色的单元格个数

activeworkbook.activesheet.[a1:c3].value = thisworkbook.activesheet.[a1:c3].valuesub qq() f = dir("d:\*.csv") do while f "" workbooks.open ("d:\" & f) k = k + 1 activeworkbook.activesheet.[a1:c3].value = thisworkbook.activesheet.[a1:c3].value

Sub js() Dim rng As Range Dim i As Integer With ActiveSheet For Each rng In .UsedRange If rng.Interior.ColorIndex <> xlNone Then i = i + 1 End If Next End With MsgBox "填充数量为:" & iEnd Sub

提供示例代码如下: 测试效果:

sub s() dim a(3) t = [a1] for i = 0 to 3 a(i) = mid(t, i + 1, 1) next for i = 2 to 85 ff = true for j = 0 to 3 f = false for k = j * 7 + 11 to j * 7 + 17 if cells(k, i).text = a(j) then cells(k, i).font.colorindex = 3 f = true end if next ff = ff and f next if ff then cells(39, i).resize(7)

如图,按CTRL+F3,新建名称aa,这个名称可以随便取.然后引用位置输入=GET.CELL(63,A1),然后在B1输入公式=aa,公式下拉到数据最后,再在C1输入公式=COUNTIF(B1:B8,">0").

方法如下:1.在E列中统计每行填充红色的单元格个数2.Alt+F11,输入代码如下:3.得到结果如下:

1. 点击公式,打开名称管理器,新建名称”color“,引用位置填写”=GET.CELL(38,Sheet2!A1)“;2. 新建辅助列B,在B1中输入”=color“,下拉填充B列;3. 利用countif统计各种颜色的个数.举例:统计黄色个数,黄色对应的是6,在D1中输入”=COUNTIF(B1:B23,6)“回车即可求出黄色为3个.

i = 0 For Each c In Range("a" & [a1048576].End(xlUp).Row & ":bz" & [a1048576].End(xlUp).Row) If c.Interior.ColorIndex <> -4142 Then i = i + 1 Next [b2] = "【" & i & "】"

楼主:你好,对于你这个问题有个前提条件:如果你的excel版本是2003版,那么你只能用系统预设的56色.用vba代码实现的其他填充色会被转换为和这56色最接近的一种.如果你的excel版本是2007及以上,那么单元格填充色几乎不受限制,

用排列筛选就可以

网站地图

All rights reserved Powered by www.5129.net

copyright ©right 2010-2021。
www.5129.net内容来自网络,如有侵犯请联系客服。zhit325@qq.com