Conditional Formatting En Masse !

[Rob-UK]

Hi all Excel Gurus out there!

I help collect/prepare data on our Vineyards. At various times of the year, the Vineyard Manager updates me on each Vines condition. This then gets translated onto an excel 'map' of each Vineyard, with one vine = 1 cell , and showing by colour the condition of that vine. For example, if a vine was deemed to be dead in Jan 2015 , and marked as such on the Data sheet ( TRUE ), it would appear on the 'Variety One' sheet as a Black Background ith White writing.

If you look at the attached Workbook, and both 'Data' and 'Variety One' sheets, you will see what I mean. To get this result I entered 80+ conditional formats, one for each row of vines, and each condition into the 'Variety One' sheet ! This will now need to be done on the other varieties ( not on workbook as size limit exceeded ), as well as the other vineyard spreadsheet, plus updated every time there is a new inspection.

I am SURE there must be a little programming, or other quicker way of doing this each time. Any help with this beast would be gratefully received. I am not a programmer, and have a moderate understanding of formulae and formatting, so be gentle with me

Thanks in advance,
Rob

[bart hoekstra]

1 VB macro for you.
Hope this is what you need.Vineyard Data.xlsm

[Rob-UK]

Wow !! Thanks so much for this amazingly fast work!

OK - so I cannot see the macro for the VB code using the DEVELOPER / MACROS or DEVELOPER / VISUAL BASIC tabs so no idea how you managed this!

It works a treat. So, my next questions is, when I add on the sheets for the next 2 varieties ( info still on the Data sheet, just different row numbers ), if I copy the sheet and just change the row/vine numbers, will it also work or do I need to do something cleverer than my brain will allow? Likewise, when I do the next lot of data collection, and add a column onto the Data sheet, will that update the variety sheets?
Sorry for the 'probably' obvious questions but this has blown my little mind !

[AlanY]

the VB code is on the 'data' sheet, see attached.

Untitled.jpg

[Rob-UK]

Thanks AlanY. Its weird as I do not see that when I open up the VB screen. From your screenshot of Bart's VB Macro, am I right in saying that it will run on any sheet, as it looks in the variety sheet for a matching row/vine number on the Data sheet and then colours it the same as the colour that appears on the Data sheet, if the condition is TRUE ?
Probably not right, but attempting to understand it.
Thanks both, and anyone else that wants to jump in !