vba搜索行及其关联的列，并突出显示条件是否满足 [英] vba searching through rows and their associated columns and highlight if conditions meet

问题描述

下面的代码将搜索一行及其关联的列.对于第7行，如果它是"N"或"TR"，并且在第12行以下所有条目都是空白，则代码将隐藏整列.

The code below would search through a row and its associated columns. For Row 7, if it is a "N" or "TR" and if all entries are blank below line 12,the code would hide the entire column.

但是，我仍然需要更多帮助！

However, I still need help with some further help!

1. 如果在第7行中有"N"或"TR"，如果在任何单元格中写入了 (而不是单独放置)，我可以突出显示其关联的内容吗?第7行中的单元格为黄色?

1. If there is a "N" or "TR" in row 7. If there is something writen in any cell, (rather than leaving it alone), can I highlight its associated cell in row 7 in yellow?

如果在第7行中有一个"Y"，如果有任何空单元格，我可以在第7行中以黄色突出显示其关联的单元格吗?

If ther eis a "Y" in row 7, If there is any empty cells, can I highlight its associated cell in row 7 in yellow?

非常感谢！特别感谢KazJaw以前关于模拟问题的帖子

Thank you so much! special thanks to KazJaw for my previous post about simular issue

Sub checkandhide()

Dim r As Range

Dim Cell As Range

Set r = Range("A7", Cells(7, Columns.Count).End(xlToLeft))

For Each Cell In r

    If Cell.Value = "N" Or Cell.Value = "TR" Then
        If Cells(Rows.Count, Cell.Column).End(xlUp).Row < 13 Then
            Cell.EntireColumn.Hidden = True
        End If
    End If
Next

End Sub

电子表格的附加示例

推荐答案

在这里，您的代码有所改进(尽管我可能需要进一步的说明……请阅读下文).

Here you have an improved version of your code (although I might need further clarifications... read below).

Sub checkandhide()

    Dim r as Range, Cell As Range, curRange As Range

    Set r = Range("A7", Cells(7, Columns.Count).End(xlToLeft))

    For Each Cell In r

        Set curRange = Range(Cells(13, Cell.Column), Cells(Rows.Count, Cell.Column)) 'Range from row 13 until last row in the given column
        If Cell.Value = "N" Or Cell.Value = "TR" Then
            If Application.CountBlank(curRange) = curRange.Cells.Count Then
                Cell.EntireColumn.Hidden = True
            Else
                Cell.Interior.ColorIndex = 6 'http://dmcritchie.mvps.org/excel/colors.htm
            End If
        ElseIf Cell.Value = "Y" Then
            If Application.CountBlank(curRange) > 0 Then
                 Cell.Interior.ColorIndex = 6 'http://dmcritchie.mvps.org/excel/colors.htm
            End If
        End If
    Next

End Sub

我不确定我是否正确理解了您的说明，因此在此我将描述此代码的确切作用；请评论任何与您想要的不完全相同的问题，以便我可以相应地更新代码:

I am not sure if I have understood your instructions properly and thus I will describe here what this code does exactly; please, comment any issue which is not exactly as you want and such that I can update the code accordingly:

它将查找范围 r 中的所有单元格.

It looks for all the cells in range r.

• 如果给定的单元格(可能位于第7行或它下面的任何其他行)满足条件之一，则将执行相应的操作.
• 部分条件取决于 curRange ，它定义为第13行到电子表格末尾之间的所有行.
• 特定条件:
  a)如果当前单元格的值为N或TR.如果 curRange 中的所有单元格都为空，则当前列处于隐藏状态.如果至少存在一个非空白单元格，则给定单元格的背景色将设置为黄色.
  b)如果当前单元格的值为Y，并且 curRange 中至少有一个单元格不是空白，则背景单元格的背景色将设置为黄色.

• If the given cell (which might be in row 7 or in any other row below it) meets one of the conditions, the corresponding actions would be performed.
• Part of the conditions depends on curRange, which is defined as all the rows between row number 13 until the end of the spreadsheet.
• Specific conditions:
  a) If the value of the current cell is N or TR. If all the cells in curRange are blank, the current column is hidden. If there is, at least, a non-blank cell, the background color of the given cell would be set to yellow.
  b) If the value of the current cell is Y and there is, at least, one cell in curRange which is not blank, the background color of the background cell would be set to yellow.

这篇关于vba搜索行及其关联的列，并突出显示条件是否满足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！