对于每个循环将无法在一个工作表上搜索价值，并在另一个工作表上更改值 [英] For each Loop Will Not Work Search for Value On one Sheet and Change Value on another Sheet

问题描述

我有一张表3列A的真假值列表和表2列A上的代码列表。如果表3中的值为A5，那么我希望第2页A5上的值应该是彩色的红。而如果表3 A6的值为= True，那么我希望纸张2 A6上的值应该是红色的。这应该在第2页和第3页的第A列中向下移动，直到数据用完为止。到目前为止，我已经得到它在第一列中的第一个单元格工作，但不能让For Each循环工作。任何帮助将不胜感激。

  Sub compare_cols（）
 
 Dim myRng As Range 
 Dim lastCell As Long 
 
'获取最后一行
 Dim lastRow As Integer 
 lastRow = ActiveSheet.UsedRange.Rows.Count 
 
'Debug。打印最后一行是& lastRow 
 
 Dim c As Range 
 Dim d As Range 
 
 Set c = Worksheets（Sheet3）。Range（A5：25）
设置d =工作表（Sheet2）。范围（A5：25）
 
 
 Application.ScreenUpdating = False 
 
对于每个单元格c 
对于每个单元格d 
 
如果c.Value = True然后
 d.Interior.Color = vbRed 
如果
 
下一个
下一个
 
 Application.ScreenUpdating = True 
 
 End Sub 
  

解决方案

一个更有效的解决方案不一定在下一个2循环之中。相反，循环遍历您要检查的范围，并引用单元格地址属性来标识要突出显示的新单元格。

查看下面的代码，让我知道如果你明白

  Sub ColorOtherSheet（）
 Dim wsCheck As Worksheet 
 Dim wsColor As Worksheet 
 Dim rngLoop As Range 
 Dim rngCell As Range 
 
设置wsCheck = Worksheets sheet3）
设置wsColor =工作表（Sheet2）
设置rngLoop =相交（wsCheck.UsedRange，wsCheck.Columns（1））
 
对于每个rngCell在rngLoop 
如果rngCell.Value = True然后
 wsColor.Range（rngCell.Address）.Interior.Color = vbRed 
结束如果
下一个rngCell 
 
 
 End Sub 
  

I have a list of true and false values on sheet 3 column A and a list of codes on sheet 2 Column A. If the value on sheet 3 A5 is = True then I want the value on sheet 2 A5 should be colored red. And If the value on sheet 3 A6 is = True then I want the value on sheet 2 A6 should be colored red. And this should move down along Column A on sheet 2 and sheet 3 until data runs out. So far i have got it to work for the first cell in column A but can not get the For Each loop to work. Any Help would be greatly appreciated.

Sub compare_cols()

    Dim myRng As Range
    Dim lastCell As Long

    'Get the last row
    Dim lastRow As Integer
    lastRow = ActiveSheet.UsedRange.Rows.Count

    'Debug.Print "Last Row is " & lastRow

    Dim c As Range
    Dim d As Range

    Set c = Worksheets("Sheet3").Range("A5:25")
    Set d = Worksheets("Sheet2").Range("A5:25")


    Application.ScreenUpdating = False

     For Each cell In c
     For Each cell In d

            If c.Value = True Then
            d.Interior.Color = vbRed
            End If

Next
Next

    Application.ScreenUpdating = True

End Sub   

解决方案

A more efficient solution wouldn't necessarily next 2 loops within each other. Instead, loop through the range that you'd like to check, and reference the cells Address property to identify new cells to highlight.

Check the code below and let me know if you understand it

Sub ColorOtherSheet()
    Dim wsCheck As Worksheet
    Dim wsColor As Worksheet
    Dim rngLoop As Range
    Dim rngCell As Range

    Set wsCheck = Worksheets("Sheet3")
    Set wsColor = Worksheets("Sheet2")
    Set rngLoop = Intersect(wsCheck.UsedRange, wsCheck.Columns(1))

    For Each rngCell In rngLoop
        If rngCell.Value = True Then
            wsColor.Range(rngCell.Address).Interior.Color = vbRed
        End If
    Next rngCell


End Sub

这篇关于对于每个循环将无法在一个工作表上搜索价值，并在另一个工作表上更改值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！