VBA Excel格式范围找到值时 [英] VBA Excel Format Range when value is found

查看:262
本文介绍了VBA Excel格式范围找到值时的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图实现一个在大量数据中寻找单词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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆