Dynamically color cell on Summary sheet based on column values on data sheet

Hello all,
I have attached an example to review. What currently happens is when the Process button is pressed on the 2_Summary sheet, the code takes data from the 1_ImportedData sheet, creates a new sheet for each customer, and populates the sheet with the customer's data. Then a link to the customer sheet is placed on the 2_Summary sheet. I need the cell on the 2_Summary sheet with the link to be colored based on some data counts on the customer sheet. On the customer sheet, I am looking for various values and if the value matches a specified number, the linked cell automatically changes to the desired color.

Thanks,
Andrew

First of all, alter the code so that the name shown on the link matches the name of the tab created.

Then use conditional formatting as shown in the pictures.

Thanks dflak. The names on the tabs and the links on the 2_Summary sheet are created dynamically and this will be done every time after the tab has been created. I will try the conditional formatting and formulas indicated.

One of the ways to apply conditional formatting automatically to newly-generated data is to put that data into an Excel table. Tables automatically copy down formatting, conditional formatting, formulas, validations, etc. as lines are added to the table.

Here is some information on working with Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

What INDIRECT does is take what it sees inside the parenthesis and interprets it as a range. So if Cell A4 contains the string "Jim" then '"&A4&"'!E1" becomes 'Jim'!E1. INDIRECT("'Jim'!E1") is the same as typing ='Jim'!E1. It is a way of dynamically building the range name for where you want to look.

I did not see any conditional formatting applied in the new sheets.

Ok, I added in conditional formatting to some of the sheets. This gives me the initial colors I need to check before coloring the 2_Summary link.

Ok. I added a conditional formatting to cell A4 on 2_Summary with the following formula:

Please Login or Register  to view this content.

If I follow this correctly, it will compare the value of Andy_Palmer cell D2 to Andy_Palmer cell D3 and turn cell A4 on 2_Summary green (because both cells contain a 3...but it is not working. I get no errors but A4 is not changing color.

You are almost correct in your interpretation, but: you do not need to include the "2_Summary" part nor do you have to include the Andy_Palmer part. The best way to work with indirect is to develop the string you want to interpret first and then copy and paste into the formula where you need it.

The correct formula is =INDIRECT("'"&A4&"'!$D$2")=INDIRECT("'"&A4&"'!$D$3") and this should be applied to Cells A4:A7.

Your formula works of course...and now I think I see where I was getting confused. I wanted to specify each tab because I did not see how this was done in the formula. Since A4 contains the name of the tab, it is used locally to set the value to be used and the ! is used to specify the tab of the same name with the $D$2 and $D$3 being compared on the tab instead of the 2_Summary sheet. Did I explain this correctly? This is a cool method of creating the conditional formatting dynamically!!

Since this only works with values and not colors, I can probably create a helper column with code that counts colors on the customer tab and use the conditional formatting to update the summary tab based on the number of total red, yellow, green counted.