Copy Conditional Formatting Excel 2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

An example is best: I want to be able to test b6 through k6 for duplicate
values and highlight the duplicates. Then I want to test b7 through k7 for
duplicate values and highlight the duplicates. ...etc. Doing each of these
ranges one at a time is easy. But I have lot of them. I would like to use
something like $b6 through $k6 and copy to the subsequent rows. The relative
reference (6) does not increment when copied to more than one row at a time.
I used to do this in 2003 but have been unable to make it work in 2007.
 
Hi

Mark the range you want to apply the CF.
Home tab>Styles>Conditional Formatting>New rule>Use formula to determine
which cells to format>formula>=COUNTIF(B6:K6,B6:K6)>1
 
Are you trying to highlight only the first of a duplicate pair, Roger?

If you want to highlight both instances, then perhaps something like
=COUNTIF($B6:$K6,B6:K6)>1, or just =COUNTIF($B6:$K6,B6)>1 ?

[Or does 2007 behave differently in this respect from 2003?]
 
Hi David

Did you see my subsequent posting? I realised straight after posting that I
had inadvertently copied B6:K6 twice.
2007 behaves the same as 2003 - just slower <bg>.

--
Regards
Roger Govier



David Biddulph said:
Are you trying to highlight only the first of a duplicate pair, Roger?

If you want to highlight both instances, then perhaps something like
=COUNTIF($B6:$K6,B6:K6)>1, or just =COUNTIF($B6:$K6,B6)>1 ?

[Or does 2007 behave differently in this respect from 2003?]
--
David Biddulph

Roger Govier said:
Hi

Mark the range you want to apply the CF.
Home tab>Styles>Conditional Formatting>New rule>Use formula to determine
which cells to format>formula>=COUNTIF(B6:K6,B6:K6)>1
Click to expand...
Click to expand...
 
Yes, I eventually saw your posting, but not until after my message was sent.
I'm glad we were in agreement.
--
David Biddulph

Roger Govier said:
Hi David

Did you see my subsequent posting? I realised straight after posting that
I had inadvertently copied B6:K6 twice.
2007 behaves the same as 2003 - just slower <bg>.
Click to expand...
David Biddulph said:
Are you trying to highlight only the first of a duplicate pair, Roger?

If you want to highlight both instances, then perhaps something like
=COUNTIF($B6:$K6,B6:K6)>1, or just =COUNTIF($B6:$K6,B6)>1 ?

[Or does 2007 behave differently in this respect from 2003?]
--
David Biddulph
Roger Govier said:
Hi

Mark the range you want to apply the CF.
Home tab>Styles>Conditional Formatting>New rule>Use formula to determine
which cells to format>formula>=COUNTIF(B6:K6,B6:K6)>1
Click to expand...
An example is best: I want to be able to test b6 through k6 for
duplicate
values and highlight the duplicates. Then I want to test b7 through k7
for
duplicate values and highlight the duplicates. ...etc. Doing each of
these
ranges one at a time is easy. But I have lot of them. I would like to
use
something like $b6 through $k6 and copy to the subsequent rows. The
relative
reference (6) does not increment when copied to more than one row at a
time.
I used to do this in 2003 but have been unable to make it work in 2007.
Click to expand...
Click to expand...
Click to expand...
 
I'll be a little more explicit. I have a spreadsheet used to schedule duties
of students in various rooms. It is very easy to double schedule someone. I
had this working in Office 2003 using COUNTIF($B6:$K6,B6)>1 which if true
would change the fill color notifying me of the duplicate entry. I was able
to copy downward and across so that the cell in the row below would contain
the conditional formatting formula COUNTIF($B7:$K7,B7) and the cell in same
row but in the next column would contain the formula COUNTIF($B6:$K6,C6)>1.
Now when I copy the formatting the relative part of the formula does not
increment at all.

Also I have just discovered that I cannot make this particular formula work
in Excel 2007 regardless even with manual entry. Argh!

If I highlight the row section over which I want to check for duplicates and
use the new duplicate values rules all works well. I can even copy this
formatting to one other row at a time and have it applied to the correct
range. However - If I try to copy it to multipe rows, the search range is
altered to include the group of rows. As each row represents a particular
time slot, I want the applicable range to ONLY be in the same row.

This may be a 'nut in the chair' problem and I have spent some time on it as
getting it working will ultimately save me more time but I am about to give
up.
 
Hi

It does work in XL2007.
Did you try selecting all the cells first as per my previous posting?

Alternatively, having set it up for one cell, Copy that cell, then Paste
Special>Formatting to the other cells.

If you can't get it to work, send me your file and I will set it up for you.
To mail direct use
roger at technology4u dot co dot uk
Do the obvious with at and dots.
 
Back
Top