Conditional formatting help

**Steve20SA** · 11-17-2020, 05:29 AM

Hi all,

Need help on the attached spreadsheet

I have a conditional format which I am struggling to copy across, I have to manually change the cells. Is there another way of easily copying the formula across so it changes.

I've attached the spreadsheet

If you select from the subject in C5 it will change the colour of the below columns, however when I copy the cell to the E5 column, I have to manually change the the Cell.

Is there a way of copying the conditional format so I do not need to manually change?

Please help

**Pete_UK** · 11-17-2020, 06:31 AM

In the block C5:D11 you have 9 CF conditions, which are all dependent on $C$5 being a particular value. Thus if you try to copy those conditions to another cell outside the block, they will still refer to cell $C$5.

If you want to make the conditions relative to C5, you will need to set up the same conditions in each cell of that range (individually), but each formula should have a reference to C5 and not $C$5. Looking at your titles across the year groups, you might need to use a formula to look at the left hand part of the title of the block, e.g.:

=LEFT(C5,3)="Sci"

Then you could use the Format Painter icon from that block to apply those conditions to another block of the same size.

From year 10 onwards you have used a different sized block, so you will need to repeat this for the third column.

It might be quicker to just colour the blocks manually after completing the timetable, as you seem to have done for this sample.

Hope this helps.

Pete

**Kaper** · 11-17-2020, 06:38 AM

Here another approach:
You could prepare one rule for each subject which will be valid spreadsheet-wide (or to optimize speed - probably Year-wide as each year there is probably a different set of subjects.

Use COUNTIF in upper left cell of first subject and remember to use relative adress (so without $ signs) showing COUNTIF range which extends one column left and 7 rows up (from this upperleft cell). I did it only for Art, D&T and PSHCE
Of course you shall remove the "old" rules.

See screenshot (note that in my Excel version arguments in functions are semicolon separated), but once you open a file and check formating rules you will see them following rules for separators in your version.

A file with first three rules is also attached.

**Steve20SA** · 11-17-2020, 06:48 AM

Brilliant Thank you Kaper, think this has solved the problem

**Kaper** · 11-17-2020, 08:48 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**Pepe Le Mokko** · 11-25-2020, 03:23 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

PS the cross post is embedded in another thread

(Note: this requirement is not optional. No help to be offered until the link is provided.)

**Shaf** · 11-25-2020, 03:42 AM

Hi Yes,

Just to confirm, the other query deals with a different problem concerning more cells. I did put this down as solved but the other query deals with more cells.

**Glenn Kennedy** · 11-25-2020, 03:47 AM

Please confirm, are Shaf and Steve20SA one and the same person? If so, please don't use multiple identities. It is causing CONFUSION and will lead to time being wasted by all of us accusing one of your IDs of hijacking the thread of your other ID. How many accounts do you have here?

**Steve20SA** · 11-25-2020, 05:16 AM

Yes we are, sorry for the confusion. I use two different laptops and didnt know I already had an account.

Sorry

Still having problems with this.

**AliGW** · 11-25-2020, 05:23 AM

What do you mean you didn't know??? You have used both in this thread!!!

You need to have the newer one closed - you will need to PM an administrator (e.g. fdibbins) to ask for this to be done. You can and should also update your profile in the older account with the extra details you've given in the newer one.

Please also read the forum rules again, especially regarding thread duplication and cross-posting.

**Steve20SA** · 11-25-2020, 06:35 AM

Sorry for the confusion.

Have messaged them.

**Glenn Kennedy** · 11-25-2020, 06:47 AM

LoL. As long as you don't start arguing with your other self!!

**Pepe Le Mokko** · 11-25-2020, 08:49 AM

Originally Posted by Glenn Kennedy

LoL. As long as you don't start arguing with your other self!!

Meaning I can't argue with my wife any more ?

**Glenn Kennedy** · 11-25-2020, 09:06 AM

No. She's your better half, not your other self!!

**macropod** · 11-26-2020, 09:48 PM

Cross-posted at:
https://www.msofficeforums.com/excel...t-copying.html
https://www.mrexcel.com/board/thread...cross.1154364/
Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html

Conditional formatting help

LinkBack

Thread Tools

Rate This Thread

Display

Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Re: Conditional formatting help

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional Formatting based on another cells Conditional Formatting

[SOLVED] Override conditional formatting (in general, without changing the conditional formatting)

Conditional Formatting Removing Previous Conditional Formatting?

[SOLVED] Keep conditional formatting results, but remove the conditional formatting?

Opening xlsm files with conditional formatting opens with removed conditional formatting

Conditional formatting based on conditional formatting of other cells?

Conditional Referencing Cell Ranges to avoid Conditional Formatting Cells Indvidually?

Bookmarks

Bookmarks

Posting Permissions