VBA Auto change the Tab colour based on condition

ckdragon

New Member
Joined
Apr 3, 2022
Messages
37
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi there,

I have a power query table that spits out a summary table onto a Sheet named SummarySheet and the detail gets loaded into specific sheets for each type of data.

I was wondering if it is possible to have a VBA 'read' the result of the sheets and then change the tab colour of the corrosponding sheet to either red or green

Red = No matching sheet name to summary table (no new data)
Green = Matching sheet name to summary table (new data loaded in sheet)

The sheets are permanent and have the same name as the table, its just that when the data is run (every other day) you could have anywhere from 1/18 - 18/18 of the data types come through (and therefore only need to look/work on the sheets where the new data has been put).

The file is a template and gets saved down once new data is input and therefore the tables override whatever data was there before - and therefore the Summary sheet is the sheet that you use to know what data is new and imported in from the raw file

I want the VBA to read something like

If SheetName exact matches any result in table X on Sheet(SummaryTable) then change sheet tab of (sheetName) to Green
If SheetName does not exact match any result in table X on Sheet(SummaryTable) then change sheet tab of (sheetName) to Red

there are currently 18 different types of data to match for (Data 1 - Data 18) but there could be more data types in the future so would like to not hard code the names in

if this is possible adn someone can help that would amazing

Thank you!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top