通过颜色评估条件格式化单元格中的数据以返回列中的数据 [英] Evaluating data in Conditionally Formatted cells by color to return data in a column

查看:637
本文介绍了通过颜色评估条件格式化单元格中的数据以返回列中的数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我已经使用条件格式设置来在保存的名为Loops_1000的范围内将单元格设置为红色,这些范围具有重复的值。为了讨论,范围是 A1:Z100 。所以现在我可能有100个单元格,其中包含各种标签数据(例如 C-03012034 )。格式是General。我想搜索范围,并找到所有的红色单元格(背景红色= 3我相信),并将数据返回到一个空列(AA)升序。



我在本网站上查找了一个解决方案,利用名称管理器创建一个名为 CellColor 的函数。看起来像这样:

  = GET.CELL(63,INDIRECT(rc,FALSE))

然后在一个单元格中,我使用了以下内容:



<$ p $作为测试,p> = IF(CellColor = 3,EXISTING,NOT USED)。

它返回未使用,并没有返回现有(即使我尝试各种颜色对于一个简单的测试,我用红色= 3离开调色板,并将= CellColor在单元格中彩色和空白单元格,所以我的红色单元格返回值3



我需要更多的帮助来搜索范围,找到所有的红色单元格,并给我在每个红色单元格中的值,并按照升序在单个列中返回这些值(或者我可以在数据获取后简单的排序)。



所以更大的问题是宏或VBA可以解释细胞背景颜色的条件格式=重复的值??

解决方案

我确定有一个更有效的方式来编码,但这可能适用于您。这假定第1行有标题:



 / pre> 


I have used conditional formatting to shade cells red in a saved range named "Loops_1000" that have duplicate values. For discussion sake the range is A1:Z100. So now I have maybe 100 cells with various tag data in them (e.g. C-03012034). The formatting is General. I want to search the Range and find all the red colored cells (background red = 3 I believe) and return the data in them to a single empty column (AA) in ascending order.

I looked up one solution on this site utilizing Name Manager to make a function called CellColor. It looks like this:

=GET.CELL(63,INDIRECT("rc",FALSE))

Then in a single cell I used the following:

=IF(CellColor=3,"EXISTING","NOT USED") as a test.  

It returns "NOT USED" and did not return "Existing" ever (even when I tried various colors. For a simple test I colored and empty cell with Red=3 off the palette and put "=CellColor" in the cell. So my red cell returned the value "3".

I need more help to search the range, find all the red cells and give me the value in each red cell and return those values in a single column in ascending order (or I can just sort after I get the data to simplify the function).

So the bigger question is can a macro or VBA interpret the cell background color for Conditional Formatting = Duplicate values??

解决方案

I'm sure there is a more efficient way to code this but this might work for you. This assumes that row 1 has headers:

Sub Macro1()
'
' Macro1 Macro
'

'clears out the destination
    Range("B1:B100").ClearContents
'filters by color
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$A$100").AutoFilter Field:=1, Criteria1:=RGB(255, 0, _
        0), Operator:=xlFilterFontColor
'copies the visible area
    Range("A2:A100").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
'pastes into next column starting below the header
    Range("B2").Select
    ActiveSheet.Paste
'removes filer
    Selection.AutoFilter
End Sub

这篇关于通过颜色评估条件格式化单元格中的数据以返回列中的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

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