| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting color doesn't show up: 90% | Excel Worksheet Functions | |||
| Conditional Formatting to Show Red Highlight in Text Field | Excel Worksheet Functions | |||
| conditional formatting or formulae to show elapsed dates | Excel Worksheet Functions | |||
| conditional formatting w/ more than 3 conditionas, color code to a different cell | About this forum | |||
| Macro to Show Conditional Formatting Criteria | Excel Programming | |||
Linear Mode
