Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel schedule chart - 'reverse' conditional formatting
I have an excel schedule chart with the first row being consecutive dates for
a month, and multiple rows below containing colored cells indicating different tasks to be performed on certain dates. As the users create or revise the colored cells in a row (i.e. number of days scheduled for a task) I'd like to have the corresponding date or range of dates from the first row copied to a column on a separate worksheet for export to an Access database. Is there a way to do this in code? I've been trying HLookup but it doesn't seem to work quite right. Maybe something that starts with a Worksheet_Selection Change event? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel schedule chart - 'reverse' conditional formatting
By now you probably get the idea that coloring cells and trying to
get their values is not the best way to do things. You can get subroutines to check the coloring of cells that were colored with regular formatting (not with conditional formatting) at Functions For Cell Colors http://www.cpearson.com/excel/colors.htm. You can see some example using his functions on my page Color Palette and the 56 Excel ColorIndex Colors Interior Color, using Count, SUM, etc. (#count) http://www.mvps.org/dmcritchie/excel/colors.htm#count If you used your actual name it would be a lot more pleasurable helping you. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "radillac" wrote in message ... I have an excel schedule chart with the first row being consecutive dates for a month, and multiple rows below containing colored cells indicating different tasks to be performed on certain dates. As the users create or revise the colored cells in a row (i.e. number of days scheduled for a task) I'd like to have the corresponding date or range of dates from the first row copied to a column on a separate worksheet for export to an Access database. Is there a way to do this in code? I've been trying HLookup but it doesn't seem to work quite right. Maybe something that starts with a Worksheet_Selection Change event? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel schedule chart - 'reverse' conditional formatting
David, thanks for the info and links, lots of good info, but I still haven't
found exactly what I need. I'm not trying to get the values of the colored cells. Where conditional formatting reads a value in a cell and then adds color, I need a way to extract dates from the first row of the worksheet, based on which cells are colored in specific rows below the dates. As the Users schedule events or revise the scheduled events by changing the quantity of colored cells in a row, I'd like the corresponding dates from the first row copied to another worksheet so I can export them to a database without manually having to key in new dates or date changes every day. It might be easier if I could send you an example of the schedule worksheet... Richard Clark (Radillac) "David McRitchie" wrote: By now you probably get the idea that coloring cells and trying to get their values is not the best way to do things. You can get subroutines to check the coloring of cells that were colored with regular formatting (not with conditional formatting) at Functions For Cell Colors http://www.cpearson.com/excel/colors.htm. You can see some example using his functions on my page Color Palette and the 56 Excel ColorIndex Colors Interior Color, using Count, SUM, etc. (#count) http://www.mvps.org/dmcritchie/excel/colors.htm#count If you used your actual name it would be a lot more pleasurable helping you. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "radillac" wrote in message ... I have an excel schedule chart with the first row being consecutive dates for a month, and multiple rows below containing colored cells indicating different tasks to be performed on certain dates. As the users create or revise the colored cells in a row (i.e. number of days scheduled for a task) I'd like to have the corresponding date or range of dates from the first row copied to a column on a separate worksheet for export to an Access database. Is there a way to do this in code? I've been trying HLookup but it doesn't seem to work quite right. Maybe something that starts with a Worksheet_Selection Change event? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel schedule chart - 'reverse' conditional formatting
Hi Richard,
There is a User Defined Function on my condfmt.htm#identify page to get the C.F. formula by Bernie Dietrick that was expanded upon by others later, I think you will find more comprehensive formuals on Chip's page . There is one at Debra's site Conditional Formatting -- Documentation</a, Document a Worksheet's Conditional Formatting http://www.contextures.com/xlCondFormat04.html"Excel But you should be using the same formulas on your worksheet as you use for Conditional Formatting rather than running a volatile UDF to find out what they are using VBA. Sticking to Excel would be much faster. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "radillac" wrote in message ... David, thanks for the info and links, lots of good info, but I still haven't found exactly what I need. I'm not trying to get the values of the colored cells. Where conditional formatting reads a value in a cell and then adds color, I need a way to extract dates from the first row of the worksheet, based on which cells are colored in specific rows below the dates. As the Users schedule events or revise the scheduled events by changing the quantity of colored cells in a row, I'd like the corresponding dates from the first row copied to another worksheet so I can export them to a database without manually having to key in new dates or date changes every day. It might be easier if I could send you an example of the schedule worksheet... Richard Clark (Radillac) "David McRitchie" wrote: By now you probably get the idea that coloring cells and trying to get their values is not the best way to do things. You can get subroutines to check the coloring of cells that were colored with regular formatting (not with conditional formatting) at Functions For Cell Colors http://www.cpearson.com/excel/colors.htm. You can see some example using his functions on my page Color Palette and the 56 Excel ColorIndex Colors Interior Color, using Count, SUM, etc. (#count) http://www.mvps.org/dmcritchie/excel/colors.htm#count If you used your actual name it would be a lot more pleasurable helping you. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "radillac" wrote in message ... I have an excel schedule chart with the first row being consecutive dates for a month, and multiple rows below containing colored cells indicating different tasks to be performed on certain dates. As the users create or revise the colored cells in a row (i.e. number of days scheduled for a task) I'd like to have the corresponding date or range of dates from the first row copied to a column on a separate worksheet for export to an Access database. Is there a way to do this in code? I've been trying HLookup but it doesn't seem to work quite right. Maybe something that starts with a Worksheet_Selection Change event? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reverse conditional formatting | Excel Worksheet Functions | |||
Gantt Chart in Excel. Do I need conditional formatting? | Charts and Charting in Excel | |||
Excel; How do I reverse the chart? 'A' on the left, etc. | Charts and Charting in Excel | |||
Conditional formatting of a data series in an Excel Chart? | Excel Discussion (Misc queries) | |||
Conditional Formatting a Schedule | Excel Worksheet Functions |