Solved: Colouring ranges based on data being entered by User

[Aussiebear]

How difficult would it be to generate a colour for a named range, which is detemined by the data being entered into a flat file spreadsheet?

In the attached workbook, the User will be entering data on the "Data" sheet;
Date, Pen#, Pen Area Effected, Equipment in Operation, Hours, etc. I'm hoping that from this data the Pen Number, and the section of Pen effected, can be selected on the "Map" sheet, and the relevant named range can then be coloured according to the type of machinery having been used in the operation.

Each of the Pens have four named ranges. For example if Pen 11 was the active Pen, you could have Whole of Pen (Whole), Front of Pen (Front), Under Shade (Shade), or Rear of Pen (Rear) as the options. I have gone to the trouble of nameing all of the ranges with the following concept, P11W, P11F,P11S,P11R. In the Legend section of the "Map" sheet, there is an example of the colour formatting I am seeking to apply.

[Bob Phillips]

I half get this Ted (Or half don't depending upon your perspective), but can you walk us through an example.

[Aussiebear]

Sure. A User enters data in the "data" sheet. From the data thats entered there I need the relevant Pen Number and portion of the pen to be coloured according to the equipment used in that Pen.

Have a look at the amended workbook to see the end result of what the map sheet would look like, based on the data that has been entered into the data sheet.

I need to pick up the Pen number from Column B, & the Pen Area Effected from column C, so that it can refer to the correct named range. Then the actual formatting (Colour & stripeing) is then governed by the type of Equipment used.

[Aussiebear]

Row 2 of the data sheet indicates that Pen 126, completely cleaned out, by a lLoader. (Named range will be P126W)

Row 3 of the data sheet indicates that Pen 41, completely cleaned out , by a Loader. (Named range will be P41W)

Row 4 of the data sheet indicates that Pen 127, had the front of the Pen cleaned out, by Excavator & Loader. (Named range will be P127F)

[Bob Phillips]

Here is a shot at it. I have revamped the colour key to correspond exactly to the equipment types. but Skidsteer has no key as it wasn't in your original list.

[Aussiebear]

Yes, I've missed skidsteer.... sorry about that

[Bob Phillips]

No problem, you can jsut set the appropriate colour key.

[Aussiebear]

This is a magnificent effort by you Bob. Is there anything that doesn't stump you? Many thanks for your assistance