verify and Format cell on sheet based on data from another sheet

**Lisa4legin** · 04-21-2012, 12:14 PM

Hiya!
I am trying to get an way to verify data in excel for our Theatre company .Our boxoffice system printsout a report each week for all the aisles avilable for our members to sell seats.("sheet1")
I am hoping to set up a way to check on the report (format the cell )if an aisle avilability should be a zero or none . The reference is the ("sheet1") which has the dates indicating on which dates the aisle needs to be left empty or zero .
the thing is that since the report is printed every week the aisles can change or the dates can extend to more than 10 days at a time indicating a dynamic set of data.
I was wondering how I can do this with VBA code .
thanks 4 all your help

**JBeaucaire** · 04-21-2012, 12:42 PM

I've added another column to your sheet1 to create an easy to find unique "key" of each Aisle-Date combination.

Then I highlighted the column and named that entire column AisleKey so we can use in a conditional formatting formula on another sheet.

Then I applied Conditional Formatting to sheet2 cells B3:H47 to check each cells Aisle+Day against the key column and color the cell if it is found on the Sheet1.

**Lisa4legin** · 04-22-2012, 03:45 AM

hi Sir,
thanks you for your response. This is a good method but I don't want to make any modifications on the sheet itself since the report is printed out by assistants who are not very familiar with excel. Also can this be modified with a VBA macro so I can then run it effectively every time without having to go to the steps?. I can try recording the steps with the recorder but like I mentioned the report can change every time and that could cause inconsistent data.

**Lisa4legin** · 04-23-2012, 08:36 AM

This is a good method but I don't want to make any modifications on the sheet itself

sorry what I meant was that when the report (sheet1) is printed out . I was thinking of just having a macro run form the Personal.xls sheet to do the needful rather than having another column.
So How Do I set it up so that it can be accessible to everyone??

**JBeaucaire** · 04-23-2012, 09:45 AM

Put the formula as suggested into column C and name it as shown so the conditional formatting on sheet2 can continue to work, copy it down quite a ways, far enough to handle any amount of data you might paste into A:B. Then do one of these:

1) Hide column C
2) Select columns A:B and set the PRINT AREA for sheet1 to those columns only

**Lisa4legin** · 04-23-2012, 04:29 PM

thanks Sir! Yes That Works nicely.. but Is there a way to account for a dynamic range ? to maybe try and just record my actions for the benifit of those who cannot follow the steps.

sorry for being such a pain but there are some pepole who I cannot get to follow simple tasks and automation is the only option avialble.

**JBeaucaire** · 04-24-2012, 08:02 AM

The solution given is dynamic if you follow all the instructions.

1) Put the formula as suggested into column C
2) Name it as shown so the conditional formatting on sheet2 can continue to work
3) Copy it down quite a ways, far enough to handle any amount of data you might paste into A:B.

Right? We covered that. If your data range in A:B is typically 100 rows, put the formula in column C down 1000 rows. Leave yourself room so the changing data is covered. Put it in 10,000 rows if necessary. The dynamic part is #2 - the fact that we named the entire column C so it is used in the conditional formatting test on the second sheet.