Excel schedule chart - 'reverse' conditional formatting

[radillac]

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?

[David McRitchie]

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" <[email protected]> wrote in message news:[email protected]...
> 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?

[radillac]

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" <[email protected]> wrote in message news:[email protected]...
> > 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?
>
>
>

[David McRitchie]

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
> > > 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?
> >
> >
> >