I have a spreadsheet with some conditional formatting in to highlight rows
where a duplicate NI Number has been entered. As it is highlighting the row,
there is conditional formatting behind each cell.

What I need is some code to show which cells have had their interior colour
changed due to conditional formatting before a save.

ie When the workbook is saved, it goes through all the cells on the
spreadsheet (A3:AA1000) and if the interior colour of a cell in Column S has
been changed due to conditional formatting an error will pop up and prevent
the save.

I managed to get this work for a manually changed cell, but not one changed
due to conditional formatting as it still treats it as no fill.

All help would be greatly appreciated.

cdb

set myrange = range ("A117")
for each cell in myrange
if cell.formatconditions.count > 0 then

end if
next cell

"cdb" wrote:

> I have a spreadsheet with some conditional formatting in to highlight rows
> where a duplicate NI Number has been entered. As it is highlighting the row,
> there is conditional formatting behind each cell.
>
> What I need is some code to show which cells have had their interior colour
> changed due to conditional formatting before a save.
>
> ie When the workbook is saved, it goes through all the cells on the
> spreadsheet (A3:AA1000) and if the interior colour of a cell in Column S has
> been changed due to conditional formatting an error will pop up and prevent
> the save.
>
> I managed to get this work for a manually changed cell, but not one changed
> due to conditional formatting as it still treats it as no fill.
>
> All help would be greatly appreciated.
>
> cdb

As mentioned in my previous post, every cell has conditional formatting on,
so this will flag every cell in the range up.

I need some code to state whether or not the conditional formatting has been
triggered.

"Joel" wrote:

>
> set myrange = range ("A117")
> for each cell in myrange
> if cell.formatconditions.count > 0 then
>
> end if
> next cell
>
> "cdb" wrote:
>
> > I have a spreadsheet with some conditional formatting in to highlight rows
> > where a duplicate NI Number has been entered. As it is highlighting the row,
> > there is conditional formatting behind each cell.
> >
> > What I need is some code to show which cells have had their interior colour
> > changed due to conditional formatting before a save.
> >
> > ie When the workbook is saved, it goes through all the cells on the
> > spreadsheet (A3:AA1000) and if the interior colour of a cell in Column S has
> > been changed due to conditional formatting an error will pop up and prevent
> > the save.
> >
> > I managed to get this work for a manually changed cell, but not one changed
> > due to conditional formatting as it still treats it as no fill.
> >
> > All help would be greatly appreciated.
> >
> > cdb

If you know how to do it for manual fill, see
http://www.xldynamic.com/source/xld.CFConditions.html to learn how to get it
for CF, but beware, it is not trivial.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"cdb" <(E-Mail Removed)> wrote in message
news:3FC6C147-0D3B-465E-B611-(E-Mail Removed)...
>I have a spreadsheet with some conditional formatting in to highlight rows
> where a duplicate NI Number has been entered. As it is highlighting the
> row,
> there is conditional formatting behind each cell.
>
> What I need is some code to show which cells have had their interior
> colour
> changed due to conditional formatting before a save.
>
> ie When the workbook is saved, it goes through all the cells on the
> spreadsheet (A3:AA1000) and if the interior colour of a cell in Column S
> has
> been changed due to conditional formatting an error will pop up and
> prevent
> the save.
>
> I managed to get this work for a manually changed cell, but not one
> changed
> due to conditional formatting as it still treats it as no fill.
>
> All help would be greatly appreciated.
>
> cdb

Cheers. If I was to use the CFColorCount bit, what code would I need to put
in the before save bit?

Do I use Call and call it and then set the range? And then say if the total
is greater than 0 there's an error?

Ta,

cdb

"Bob Phillips" wrote:

> If you know how to do it for manual fill, see
> http://www.xldynamic.com/source/xld.CFConditions.html to learn how to get it
> for CF, but beware, it is not trivial.
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "cdb" <(E-Mail Removed)> wrote in message
> news:3FC6C147-0D3B-465E-B611-(E-Mail Removed)...
> >I have a spreadsheet with some conditional formatting in to highlight rows
> > where a duplicate NI Number has been entered. As it is highlighting the
> > row,
> > there is conditional formatting behind each cell.
> >
> > What I need is some code to show which cells have had their interior
> > colour
> > changed due to conditional formatting before a save.
> >
> > ie When the workbook is saved, it goes through all the cells on the
> > spreadsheet (A3:AA1000) and if the interior colour of a cell in Column S
> > has
> > been changed due to conditional formatting an error will pop up and
> > prevent
> > the save.
> >
> > I managed to get this work for a manually changed cell, but not one
> > changed
> > due to conditional formatting as it still treats it as no fill.
> >
> > All help would be greatly appreciated.
> >
> > cdb
>
>
>

Instead of creating a new function etc, is there an easy way to replicate the
following formula in VB so that it can then carry out the conditional
formatting test?

=COUNTIF($S$3:$S42,$S42)>1

"cdb" wrote:

> Cheers. If I was to use the CFColorCount bit, what code would I need to put
> in the before save bit?
>
> Do I use Call and call it and then set the range? And then say if the total
> is greater than 0 there's an error?
>
> Ta,
>
> cdb
>
> "Bob Phillips" wrote:
>
> > If you know how to do it for manual fill, see
> > http://www.xldynamic.com/source/xld.CFConditions.html to learn how to get it
> > for CF, but beware, it is not trivial.
> >
> > --
> > HTH
> >
> > Bob
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> > "cdb" <(E-Mail Removed)> wrote in message
> > news:3FC6C147-0D3B-465E-B611-(E-Mail Removed)...
> > >I have a spreadsheet with some conditional formatting in to highlight rows
> > > where a duplicate NI Number has been entered. As it is highlighting the
> > > row,
> > > there is conditional formatting behind each cell.
> > >
> > > What I need is some code to show which cells have had their interior
> > > colour
> > > changed due to conditional formatting before a save.
> > >
> > > ie When the workbook is saved, it goes through all the cells on the
> > > spreadsheet (A3:AA1000) and if the interior colour of a cell in Column S
> > > has
> > > been changed due to conditional formatting an error will pop up and
> > > prevent
> > > the save.
> > >
> > > I managed to get this work for a manually changed cell, but not one
> > > changed
> > > due to conditional formatting as it still treats it as no fill.
> > >
> > > All help would be greatly appreciated.
> > >
> > > cdb
> >
> >
> >

application.Countif(Range("S3:S42"),Range("S42")) > 1

or
set rng = Range("S3:S42")
for each cell in rng
if application.countif(rng,cell) > 1 then
msgbox "There are duplicate entries"
end if
Next



--
Regards,
Tom Ogilvy


"cdb" wrote:

> Instead of creating a new function etc, is there an easy way to replicate the
> following formula in VB so that it can then carry out the conditional
> formatting test?
>
> =COUNTIF($S$3:$S42,$S42)>1
>
> "cdb" wrote:
>
> > Cheers. If I was to use the CFColorCount bit, what code would I need to put
> > in the before save bit?
> >
> > Do I use Call and call it and then set the range? And then say if the total
> > is greater than 0 there's an error?
> >
> > Ta,
> >
> > cdb
> >
> > "Bob Phillips" wrote:
> >
> > > If you know how to do it for manual fill, see
> > > http://www.xldynamic.com/source/xld.CFConditions.html to learn how to get it
> > > for CF, but beware, it is not trivial.
> > >
> > > --
> > > HTH
> > >
> > > Bob
> > >
> > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> > >
> > > "cdb" <(E-Mail Removed)> wrote in message
> > > news:3FC6C147-0D3B-465E-B611-(E-Mail Removed)...
> > > >I have a spreadsheet with some conditional formatting in to highlight rows
> > > > where a duplicate NI Number has been entered. As it is highlighting the
> > > > row,
> > > > there is conditional formatting behind each cell.
> > > >
> > > > What I need is some code to show which cells have had their interior
> > > > colour
> > > > changed due to conditional formatting before a save.
> > > >
> > > > ie When the workbook is saved, it goes through all the cells on the
> > > > spreadsheet (A3:AA1000) and if the interior colour of a cell in Column S
> > > > has
> > > > been changed due to conditional formatting an error will pop up and
> > > > prevent
> > > > the save.
> > > >
> > > > I managed to get this work for a manually changed cell, but not one
> > > > changed
> > > > due to conditional formatting as it still treats it as no fill.
> > > >
> > > > All help would be greatly appreciated.
> > > >
> > > > cdb
> > >
> > >
> > >