VBA Through Conditional Formatting+

[t0ny84]

Hi All,

I have the following scenarios that I am trying to write a code for in VBA to allow me to save time in setting up Conditional Formatting Rules for each new worksheet which is added to a workbook.

Scenario 1
I have a list of people which I want to have the cell colour change based on the cell next to it (picture below) e.g. if Column B is Yes for Name 1 then the cell for Name 1 would be Green with White Text. If No then it would be Red with White Text.

Scenario 2
When the spreadsheet is opened\closed\cell information updated a macro to run through each worksheet (current and any future created ones) within the workbook and update any cells which contents match a name from the list of people in Scenario 1 to be the same formatting.

Thanks in Advance for any assistance or guidance!
:D

 

[Dan_W]

Do you know how to make the rule for Scenario 1 (if you had to do it manually)? If so, then I would suggest using the Record Macro function in Excel to produce the basic VBA code, and then you could adjust as needed.

 

[t0ny84]

Hi Dan_W thanks for the idea, I tried a few different ways but ended up creating a template tab and then hid this with a main page and New Tab button which copies the template tab to the end of the workbook. This should minimise most of the possibilities of people stuffing up the document (I hope)!

 

[Fluff]

Cross posted Conditional Formatting Through VBA

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.