Code to show if a cell has met conditional formatting
 

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

Code to show if a cell has met conditional formatting
 

set myrange = range ("A1:D17")
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

Code to show if a cell has met conditional formatting
 

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 ("A1:D17")
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

Code to show if a cell has met conditional formatting
 

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" wrote in message
...
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

Code to show if a cell has met conditional formatting
 

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" wrote in message
...
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

Code to show if a cell has met conditional formatting
 

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" wrote in message
...
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

Code to show if a cell has met conditional formatting
 

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" wrote in message
...
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