在Excel工作簿中设置条件格式，循环遍历所有工作表 [英] Set conditional formatting in excel workbook, looping through all the sheets
问题描述
问候，
请帮助
我想做的是:(我不能用报告，因为我必须导出到Excel）
我将一些查询导出到excel工作簿中的不同选项卡
然后我循环遍历每个选项卡将autowidth应用于列并将自动筛选应用于工作簿中每个选项卡的第一行。我已经这样了
但是，我需要在工作簿中应用条件格式，我想通过循环遍历每个选项卡，应用条件格式对于每个选项卡的每一列，第二行中的单元格为
（因为第一行具有来自Access的标题），然后将其复制到该选项卡的其余列，并且然后循环遍历该选项卡中的每个列并同样应用条件格式（我必须这样做，因为列包含不同的格式）
我到目前为止：
这是一个放在表单按钮的onclick事件上的代码，它在文件保存后运行
Regards,
Please help
What I''m trying to do is this: (and I can''t use reports since I must export to Excel)
I export some queries to different tabs in an excel workbook
I then loop through each tab and apply autowidth to columns and apply autofilter to the first row of every tab in the workbook. I''ve this down
However, I need to apply conditional formatting in the workbook, I''d like to do so by looping through each of the tabs, apply the conditional formatting to
the cell in second row for each column of each tab (since the first one has the headings from Access) and then copy it to the rest of the column of that tab, and then loop through each of the columns in that tab and apply the conditional formatting likewise (I''ve to do so since the columns contain different formats)
I''ve this so far:
This is a code placed on a onclick event of a button in a form, it runs after the file has been saved
推荐答案
嗨。使用从Excel运行的Excel VBA，如果不自行运行完全相同的代码，很难告诉您可能出现的问题 - 并且您没有告诉我们您找到的是什么或者您没有找到令人沮丧。
无论如何，看看你标记为令人沮丧的部分你大量使用隐式范围选择 - .select 和选择。 DoSomething的的。这在Excel中运行良好 - 事实上，如果你使用宏录制器来生成示例代码，它会放入很多这些代码 - 但我发现它可能会导致从另一个应用程序运行代码时出现问题，其中Excel是在其中运行的服务器应用程序背景。
在所有情况下，您都可以取消 .select 或选择。并直接引用基础范围对象管他呢。这更简单，更可靠。您不需要选择一个对象来访问其属性或设置其值，并且从Access使用Excel时也不会更快。
例如，
Hi. With Excel VBA running from Access it''s difficult to advise you on what might be wrong without running the exact same code ourselves - and you haven''t told us what it is or isn''t doing for you that you are finding so frustrating.
Anyway, looking at the section you have marked as frustrating you make considerable use of implicit range selection - .select and selection.dosomething. This works fine in Excel - and indeed if you use the macro recorder to generate example code it will put in lots of these - but I have found that it can cause problems in running code from another application where Excel is a server application running in the background.
In all cases you can do away with .select or selection. and refer instead directly to the underlying range object or whatever. This is simpler and more reliable. You don''t need to select an object to access its properties or set its values, and it is also much quicker not to when using Excel from Access.
For example,
感谢您的快速响应，这是一个没有更正代码的回复，
令人沮丧的是它显示一个''对象不支持这种方法''，lemme一直试图清理这个代码。
谢谢，
Thanks for the prompt response, and this is a reply without correcting the code,
The frustrating part is that it displays an ''object doesn''t support this method'', lemme keep trying to clean this code.
Thanks,
问候，
请帮助
我想做的是:(我可以因为我必须导出到Excel，所以不要使用报告。
我将一些查询导出到excel工作簿中的不同选项卡
然后我循环通过每个选项卡并将自动宽度应用于列并将自动筛选应用于工作簿中每个选项卡的第一行。我已经这样了
但是，我需要在工作簿中应用条件格式，我想通过循环遍历每个选项卡，应用条件格式对于每个选项卡的每一列，第二行中的单元格为
（因为第一行具有来自Access的标题），然后将其复制到该选项卡的其余列，并且然后循环遍历该选项卡中的每个列并同样应用条件格式（我必须这样做，因为列包含不同的格式）
我到目前为止：
这是一个放在表单按钮的onclick事件上的代码，它在文件保存后运行
Regards,
Please help
What I''m trying to do is this: (and I can''t use reports since I must export to Excel)
I export some queries to different tabs in an excel workbook
I then loop through each tab and apply autowidth to columns and apply autofilter to the first row of every tab in the workbook. I''ve this down
However, I need to apply conditional formatting in the workbook, I''d like to do so by looping through each of the tabs, apply the conditional formatting to
the cell in second row for each column of each tab (since the first one has the headings from Access) and then copy it to the rest of the column of that tab, and then loop through each of the columns in that tab and apply the conditional formatting likewise (I''ve to do so since the columns contain different formats)
I''ve this so far:
This is a code placed on a onclick event of a button in a form, it runs after the file has been saved
这篇关于在Excel工作簿中设置条件格式，循环遍历所有工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
