Excel VBA:Excel 2007中的sheet2值中的sheet1单元格的条件格式 [英] Excel VBA : conditional formatting of sheet1 cells from sheet2 values in excel 2007

问题描述

问题1: 我尝试使用以下公式从sheet2 A1格式化sheet1中的单元格A1(彩色格式):

Issue 1: I tried to format cell A1 ( color format) in sheet1 from sheet2 A1 with something following formula:

=Sheet2!A1>0.5

Excel警告您不能将其他工作表或工作簿中的公式用作条件公式.

The excel warned that you can not use formula from other sheet or workbook for conditional formula.

此问题是否有VBA解决方案?

Is there is any VBA solution to this problem?

问题2:正在寻找替代问题1的过程 我可以按照

Issue 2: in process of looking alternative to issue 1 I can rearrange the data (I have 6000 columns) in the way that

Sheet3$A1 = Sheet1$A1, Sheet3$B1 = Sheet2$A1

意味着工作表1和工作表2中的第一列将是工作表3中的第一列和第二列.是否有任何简单的解决方案，也许使用vba.

Means that the first column in sheet 1 and sheet 2 will be first and second column in sheet 3. Is there is any easy solution to this, perhaps using vba.

一旦实现，我想成对应用条件格式

Once this is acheived I want to apply the condition format in pairs

基于B格式化的列，基于D列格式化的C列，等等...直到所有6000对都完成了...再次，我正在寻找自动化.

A column formatted based on B, C column formatted based on D coumn and so on ......till all 6000 pairs are done...again I am looking for automation.

对不起，我对Excel VBA的了解有限，非常感谢您的帮助

Sorry I have limited knowledge to Excel VBA, your help is much appreciated

推荐答案

在另一张纸上可以解决条件格式引用:使用Named Range

There is a work around for conditional format references on another sheet: use a Named Range

以您的情况为例，
在工作表2中将整个A列命名为Sheet2_ColA
将条件格式公式应用于工作表1中的单元格

In your case, try this
Name the whole of column A in Sheet 2 as Sheet2_ColA
Apply conditional format formula to cells in Sheet 1

=INDEX(Sheet2_ColA,ROW())>0.5

修改

要应用于整个工作表，请选择一个看起来不像保留字的名称(例如AllSheet2)，然后将公式更改为对行和列进行索引

to apply to whole sheet, choose a name that doesn't look like a reserved word (eg AllSheet2) and change formula to index rows and columns

=INDEX(AllSheet2,ROW(),COLUMN())>0.5

这篇关于Excel VBA:Excel 2007中的sheet2值中的sheet1单元格的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！