VBA Excel格式范围找到值时 [英] VBA Excel Format Range when value is found
问题描述
我试图实现一个在大量数据中寻找单词TRUE和FALSE的宏,然后在找到的时候改变它上面的单元格的颜色。
具体来说,我希望它的颜色不是TRUE / FALSE-cell,而是直接在它上面的30个单元格。这是事情变得棘手的地方...我希望有人可以帮助。
我已经尝试过适应下面的代码,但大多数情况下,我将它作为灵感添加点。
Sub ChangeColor()
lRow =范围(C& Rows.Count).End(xlUp) .Row
Set MR = Range(C2:C& lRow)
对于每个单元在MR
中选择个案单元格。值
个案是
cell_colour = 4
Casey
cell_colour = 4
Case else
cell_colour = 3
End Select
cell.Interior.ColorIndex = cell_colour
Next
End Sub
使用数据字段数组
循环遍历范围总是很耗时间;这应该加快速度。 注意:格式化单个单元格可以最大化文件大小,所以至少我把整个C列重新格式化为 xlColorIndexNone
。
$ b $ pre $ code $ Option $ Exp
$ b $ Public Sub Mark30CellsAbove(
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(MySheet)
Dim v As Variant
Dim i As Long,j As Long,n As Long,m As Long,r As Long
Dim Rng作为范围
Dim t As Double
'stop watch
t = Timer
'获得C列中的最后一行C
n = ws.Range(C& ws。 Rows.Count).End(xlUp).Row
'将数值赋给一个基于2dim的数组
v = ws.Range(C1:C& n).Value
'clear颜色在WHOLE列以最小化文件大小
ws.Range(C:C)。Interior.ColorIndex = xlColorIndexNone
'通过C2循环:Cn并在找到条件之前标记30行
对于i = 2到n
'检查条件,查找字符串true或false
如果InStr(。true.false。,。& LCase(v(i,1 ))&。)> 0然后
'设置范围块 - 固定行数30上面找到的单元
如果i < 32然后'只有少于30行
设置rng = ws.Range(C2:C&(i - 1))
否则
设置rng = ws.Range (C&(i-30)&:C&(i-1))
End If
rng.Interior.ColorIndex = 4
End If
Next i
MsgBox所需时间:&格式(定时器 - t,0.00)& 秒。
End Sub
当然,您也可以在 / code> -
EndIf
,只是为了查看这个较慢的方法:
<$ c如果InStr(。true.false。,。& LCase(v(i,1))&。)> 0然后
'总是避免循环遍历一个范围
'对于j = i - 1到i - 30步骤-1
'如果j < 2然后退出For'可选转义,如果标题行下面的一行
'ws.Cells(j,3).Interior.ColorIndex = 4
'Next
End If
I'm trying to implement a macro that looks for the words "TRUE" and "FALSE" in a huge array of data - and then, when found, changes the color of the cells above it.
Specifically, I would like it to color not the TRUE/FALSE-cell, but the 30 cells directly above it. This is where things get tricky... I hope someone can help.
I've tried adapting the below code, but mostly I'm adding it as inspiration at this point.
Sub ChangeColor()
lRow = Range("C" & Rows.Count).End(xlUp).Row
Set MR = Range("C2:C" & lRow)
For Each cell In MR
Select Case cell.Value
Case "Yes"
cell_colour = 4
Case "y"
cell_colour = 4
Case Else
cell_colour = 3
End Select
cell.Interior.ColorIndex = cell_colour
Next
End Sub
Using a datafield array
Looping through a range is always time consuming; this should speed it up.
Caveat: Formatting single cells can maximize file size, so at least I reformat the whole column C to xlColorIndexNone
.
Option Explicit
Public Sub Mark30CellsAbove()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet")
Dim v As Variant
Dim i As Long, j As Long, n As Long, m As Long, r As Long
Dim Rng As Range
Dim t As Double
' stop watch
t = Timer
' get last row in column C
n = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
' get values to one based 2dim array
v = ws.Range("C1:C" & n).Value
' clear existing colors over the WHOLE column to minimize file size
ws.Range("C:C").Interior.ColorIndex = xlColorIndexNone
' loop through C2:Cn and mark 30 rows before found condition
For i = 2 To n
' check condition, find string "true" or "false"
If InStr(".true.false.", "." & LCase(v(i, 1)) & ".") > 0 Then
' set range block - fixed rows count 30 above found cell
If i < 32 Then ' only in case of less than 30 rows
Set rng = ws.Range("C2:C" & (i - 1))
Else
Set rng = ws.Range("C" & (i - 30) & ":C" & (i - 1))
End If
rng.Interior.ColorIndex = 4
End If
Next i
MsgBox "Time needed: " & Format(Timer - t, "0.00") & " seconds."
End Sub
Of course you could also loop within If
- EndIf
, just to see this slower method:
If InStr(".true.false.", "." & LCase(v(i, 1)) & ".") > 0 Then
' Always avoid to loop through a range
' For j = i - 1 To i - 30 Step -1
' If j < 2 Then Exit For ' optional escape if one line under title row
' ws.Cells(j, 3).Interior.ColorIndex = 4
' Next
End If
这篇关于VBA Excel格式范围找到值时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!