【v1-6综合练习题1】如何按条件填充颜色

兰色幻想

<p>&nbsp;&nbsp; 题目和要求看下图，做完后直接跟贴把代码贴出来，不必上传文件。方法很多，看谁的最简单。</p><p>&nbsp;&nbsp;</p><p> gLKZOSaS.rar (1.92 KB, 下载次数: 127)

2007-12-16 11:41 上传

点击文件名下载附件
[v1-6综合练习题1]如何按条件填充颜色 <br/></p><p><font color="#1010de">&nbsp;&nbsp;注： </font></p><p><font color="#1010de">&nbsp; 1、循环还不熟悉的可以来视频区看看第10讲和第11讲的循环视频讲解，特别是有一种非常有用的循环方式上课时没有涉及到：for..each in ..next循环。</font></p><p><font color="#1010de">&nbsp; 2、填充颜色也没有讲，但大家可以用录制宏录制出代码。</font></p><p><a href="http://www.excelpx.com/forum.php?mod=forumdisplay&fid=136">http://www.excelpx.com/forum.php?mod=forumdisplay&fid=136</a></p>

[此贴子已经被作者于2007-12-16 11:46:43编辑过]

为工作

<p>谢谢兰版 .............</p><p>我的好好练练................</p><p>&nbsp;</p>[em23][em23][em23]

zgwei050

<p>希望高手们晚点帖程序，给我等新手一点时间</p><p>&nbsp;</p><p>看了5楼以下的各种代码，真棒，学习了！</p>

[此贴子已经被作者于2007-12-16 13:34:07编辑过]

zgwei050

Sub 单元格填充颜色()<br/>Dim mrg As Range<br/>&nbsp; With Sheets("sheet1")<br/>&nbsp;&nbsp;&nbsp; For Each mrg In Union(.Range("A1:C9"), .Range("D10:F21"))<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If mrg.Value &gt; 100 Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mrg.Interior.ColorIndex = 4<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp; Next mrg<br/>&nbsp; End With<br/>End Sub

[此贴子已经被作者于2007-12-16 12:21:18编辑过]

hhzjxss

<p>来两个：</p><p>Sub 填充颜色_UsedRange() '适用于所有已用过的单元格区域<br/>&nbsp;&nbsp;&nbsp; Dim rng As Range<br/>&nbsp;&nbsp;&nbsp; With Sheet1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For Each rng In .UsedRange<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If rng.Value &gt; 100 Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rng.Interior.ColorIndex = 4 '绿色<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next rng<br/>&nbsp;&nbsp;&nbsp; End With<br/>End Sub</p><p>Sub 填充颜色_CurrentRegion() '只适用于相邻连续区域<br/>&nbsp;&nbsp;&nbsp; Dim rng As Range<br/>&nbsp;&nbsp;&nbsp; With Sheet1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For Each rng In .Range("a1").CurrentRegion<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If rng.Value &gt; 100 Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rng.Interior.ColorIndex = 3 '红色<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next rng<br/>&nbsp;&nbsp;&nbsp; End With<br/>End Sub</p>

[此贴子已经被作者于2007-12-16 13:01:56编辑过]

hong4777

<p>Sub ds()<br/>Dim rg As Range<br/>&nbsp;With Sheets("sheet1")<br/>&nbsp;&nbsp; Set rg = Union(.Range("a1:c9"), .Range("d10:f21"))<br/>&nbsp;&nbsp;&nbsp;&nbsp; rg.FormatConditions.Delete<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rg.FormatConditions.Add xlCellValue, Operator:=xlGreater, _<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Formula1:="100"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rg.FormatConditions(1).Interior.ColorIndex = 4<br/>&nbsp;End With<br/>&nbsp;&nbsp; Set rg = Nothing<br/>End Sub<br/></p>

[此贴子已经被作者于2007-12-16 13:04:21编辑过]

hhzjxss

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>hong4777</i>在2007-12-16 13:01:00的发言：</b><br/><p>Sub ds()<br/>Dim rg As Range<br/>&nbsp;With Sheets("sheet1")<br/>&nbsp;&nbsp; Set rg = Union(.Range("a1:c9"), .Range("d10:f21"))<br/>&nbsp;&nbsp;&nbsp;&nbsp; rg.FormatConditions.Delete<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rg.FormatConditions.Add xlCellValue, Operator:=xlGreater, _<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Formula1:="100"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rg.FormatConditions(1).Interior.ColorIndex = 4<br/>&nbsp;End With<br/>&nbsp;&nbsp; Set rg = Nothing<br/>End Sub<br/></p><br/></div><p></p><p>&nbsp;</p><p>条件格式</p>[em17][em17]

为工作

<p>我来过笨方法</p><p>Sub qq()<br/>Dim x As Integer, i As Integer<br/>For x = 1 To 21<br/>For k = 1 To 6<br/>If Cells(x, k) &gt; 100 Then<br/>Cells(x, k).Interior.ColorIndex = 4<br/>Else<br/>Range("a4").Select<br/>End If<br/>Next k<br/>Next x<br/>End Sub</p><p>老师录制出来了,可还不知Interior.ColorIndex = 4是什么意思?谢谢!<br/></p>

[此贴子已经被作者于2007-12-16 13:17:26编辑过]

hhzjxss

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>为工作</i>在2007-12-16 13:11:00的发言：</b><br/><p>老师录制出来了,可还不知Interior.ColorIndex = 4是什么意思?谢谢!<br/></p><br/></div><p>&nbsp; </p><p>4是红色，3是红色，你可以从0往上修改数字看看吧！</p>

为工作

<p>谢谢雨版...</p><p>那这两个单词呢??</p><p>Interior.ColorIndex </p>