Excel / VBA动态格式化整行 [英] Excel/VBA to format entire row Dynamically

问题描述

大家好，

我有2个工作簿WB1& 。WB2 在WB1中，有近100个名称范围。 这些名称范围中的每一个都使用常规，数字，百分比（带有1或2位小数）和日期格式进行格式化。

在WB2中，Sheet1从A2到A101我拥有所有这些列出的名字在wb2中。

我想写一个代码

1. 循环通过A2：A100 in wb2 sheet1和
2. 对于每个名称，它将在定义该名称的工作表中转到WB1
3. 该名称范围单元格的复制格式
4. 更改整行在具有该格式的wb2 sheet1中

例如

1. 如果wb2 sheet1中的A2具有文本" MY_NAME_2"然后
2. 转到"MY_NAME_2"在wb1中输入名称并复制该单元格的格式。如果是百分比，则
3. 将wb2 sheet1中A2的整行更改为百分比

谢谢，

Zaveri

解决方案

Hi Zaveri，

你遇到了哪个问题？据我所知，我们可以使用Range（"RangeName"）。复制目标单元格。然后我们可以使用我们复制的预览范围中的Range.EntireRow.PasteSpecial xlPasteFormats到
过去的格式。

以下链接可能对您学习Excel开发有帮助：

我如何...（Excel 2013开发人员参考）

对象模型参考（Excel 2013开发人员参考）

祝你好运

Fei

Hi All,

I have 2 workbooks WB1 & WB2.  In WB1 there are nearly 100 name ranges.  Each of these name ranges are formated with either general, number, Percentage (with 1 or 2 decimal places) and date format.

In WB2, Sheet1 from A2 to A101 i have all these names listed which are in wb2.

I want to write a code which will

1. loop through A2:A100 in wb2 sheet1 and
2. for each names it will go to WB1 in the sheet where that name is defined
3. copy format of that name range cell
4. change entire row in wb2 sheet1 with that format

for example

1. If A2 in wb2 sheet1 has text "MY_NAME_2" then
2. Go to "MY_NAME_2" name in wb1 and copy the format of that cell. If it is percentage then
3. change entire row of A2 in wb2 sheet1 to percentage

Thanks,

Zaveri

解决方案

Hi Zaveri,

Which problem did you come across? As far as I know, we can use Range("RangeName").Copy the target cell. Then we can use the Range.EntireRow.PasteSpecial xlPasteFormats to past format from the previews range we copied.

Also links below may be helpful for you learning Excel developing:

How do I... (Excel 2013 developer reference)
Object model reference (Excel 2013 developer reference)

Best regards

Fei

这篇关于Excel / VBA动态格式化整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！