Copy conditional formatting to a series of cells.

[Paul_T]

Afternoon all,

I have a formula that works for conditional formatting, a cell changes colour when a value is in another cell on a separate sheet.
I need to copy this to to the entire sheet but for the formula to change for each cell number.
At the minute if I copy/ paste special - format it copies the info for the previous cell and doesn't change the cell from eg: D9 to E9 and so on.
See attached workbook.

Any help would be appreciated.
Cheers,
Paul.

[ELeGault]

Remove the rules you have in place...

We will just use the rule from the first D8 Cell ... Unlock the evaluation since the evaluation is a consistent 1:1 comparison as you move through the range

Get rid of the $ Fixed or Absolute referencing in the Conditional formatting

Please Login or Register  to view this content.

So that it looks like this

Please Login or Register  to view this content.

Then change the APPLIES TO Range from Just D8 to the USED range in your sheet... right now it is

Please Login or Register  to view this content.

but can be extended to whatever range you are going to....


By removing those Fixed/Absolute referencing, Excel will evaluate each position relative to where it is within the range you have "Applied" it to.

[ELeGault]

I do want to point out that you are simply highlighting WHEN there is a value on Quote sheet relative to the planning sheet, while the planning sheet has no value.

This doesnt mean they have to match, simply means that you cannot have an empty cell on Planning sheet in the relative position of quote sheet IF Quote sheet has any value in it

-- Why point that out... well if this is to plan something that is not listed on the quote that would be a red flag for me... even more so when the value is different than that of the quote sheet...


If anything change it to be that the value of the Quote Sheet <> the value of Planning sheet... this way if either of them have a value that is not the same it will highlight... maybe that should be a second rule that highlights Yellow so you know this is a difference... The more I write the more I want to set this up differently... Just be mindful of what you decide to program as you can back yourself into a corner with a lack of consideration for scenarios that will be present.

[Paul_T]

Thanks for your help, this works a treat.
I'll give you my way of thinking for the spreadsheet.
I quote a job on the first sheet listing item numbers, estimated hours etc. These are all different depending on the tool I am quoting. The planning sheet only highlights the 'activity' (PGM, NC2 etc.) that is required for that particular job. When this sheet is printed it follows the job around the shop and our engineers will put their number stamp in the box highlighted for that activity, so we know which person has done which activity on that tool.
It seemed a simple way to produce a planning sheet directly from the quote sheet as it self populates.
I am open to all other ways to do this though!

Thanks,
Paul.

[euphoria5150]

I've seen this question before, let me see if I can dig it out for you. Thanks, Russ

[ELeGault]

Makes perfect sense. If this is producing a sort of "CHECK LIST" via Highlighted Boxes to indicate that this BOX must be filled for the JOB to be "Completed".

Glad it works for what you need, being that you are not entering in the live numbers onto the "Planning Sheet" there would not be a time where you would have varied values between the two sheet... simply "YES or NO" to the field be required.

- Cheers

Be sure to mark this solved if the solution above completed it so people are not attempting to recreate a solution.