Conditional formatting

Hi,

I have designed a Training Schedule for my athletes to complete using conditional formatting to make the process far quicker and user freindly to complete.

I Selected the the range B3:G32 and applied the following conditional formatting rules:

1. Cell Value - Equal to - "Performance Centre" - Format (gold fill/red font)
2. Cell Value - Equal to - "England Performance Training" - Format (tan fill/red font)
3. Cell Value - Equal to - "Own Training" - Format (blue fil/red font)
4. Cell Value - Equal to - "S&C/Physical" - Format (green fill/red font)
5. Cell Value - Equal to - "Study" - Format (lemon fill/red font)

In column A3:A32 is times on the 1/2 hour (7.00am - 10.00pm). If a certain training type were to be input into the CF range for a 2 hr slot it would require 4 cells to be merged to correspond with the times in column A or if a 3 hr slot it would require 6 cells to be merged ....

If I/they merge cells and input say "Study" the formatting works perfect. However once I start to merge more cells relevant to their time slots the formatting does not work

I then referred back to "CF manage rules" and the original rules/range set (B3:G32) had become some random range of cell values

Any help please!??!

Col

Originally Posted by teylyn

If I select a few cells, merge them, then enter the text, the conditional formatting will show. Does it not do that for you?

The problem is if you merge cells say B7:B10(9.00am-11.00am) for a certain slot then you were to come back to it and want to change the schedule so you then unmerged the cells and merged B8:B10(09.30am-11.00am) the formatting will not work. If you click the CF tab/manage rules you will see the "applies to" range is totally different from the range originally formatted (B3:G32).

Same seems to happen if I cut/copy & paste cells.

Have reattached file to show what I mean!!
Col


Example of weekly plan only.xlsx

Any ideas out there??

Sorry to be so annoying but anyone got any suggestions!!

Not sure if I am being a bit stupid here lol but I kind of don't get what you have said. I wanted to send out the spreadsheet to 20+ athletes and the fill/font would be inputted for them, I wanted to have the table always formatted that any change could take place at any time(slots moved to different times) and the CF originally set would always be followed.

Or do you think I am complicating a simple procedure and it would be easier to just merge cells manually and format with the colour fill/text relevant to the key

Appreciate your help greatly

Col

Originally Posted by teylyn

Ok, that confirms my suggestion not to use merged cells.

I think you need a different approach alltogether.

Don't use merged cells. Enter the category into a single cell and copy it down as many cells as required.
Use another set of conditional formatting to make the cell font color the same as the cell background color if the cell content is the same as the cell above. The text will not appear centered in the coloured block, but that's about the only difference.

The upside is that you only have one contiguous range in the CF definitions. The cell merging changes that.

Would that work for you?

Attached new worksheet

Originally Posted by teylyn

Please be aware that members here have obligations outside of the voluntary work conducted in this forum. Please do not use Private Messaging to bump your question. Also, members here live around the world and may not be on-line when you are. So, please be patient and polite instead of pushy.

Attached please find a copy of your latest workbook with two additional conditional formats applied to the range of $B$3:$G$32

=AND(B3="S&C/Phy",B2="S&C/Phy") -- format to green fill and green text
=AND(B3="PC",B2="PC") -- format to orange fill and orange text

Create rules along the same lines for your other categories.

Enter a category in any cell and copy it down for as many cells as required. The effort is the same as selecting several cells and merging them. So it should do what you want, with the slight difference that the visible text sits in the topmost cell of the block instead of being centered vertically.

Do NOT use Private Messaging to reply to this suggestion.

cheers,

Thank you for the help. Sorry to Private Message you I did not realise this was not the way to go about getting reply!!

The table looks well good and is so easy to complete Have attached the worksheet with your great advise applied!!

Thanks again
Col