Macro for Changing Cell Background Color

Hi Rick,

If you're happy to use conditional formats, why not use 3 FC's

FC1 checked and Today
FC2 checked
FC3 today

where Checked refers to the Checkbox linked cell or some other value you
change by code with the checkbox and today = cell's date (IsFormula).

Regards,
Peter T


<RSummersJr@gmail.com> wrote in message
news:1133535612.437374.54420@g49g2000cwa.googlegroups.com...
> Hello,
>
> I'm trying to setup a macro that will automatically change the color of
> a cell's background when the cell's date data equals TODAY(). I know I
> can do this with conditional formatting, but I also need it to change
> to another color once a checkbox has been checked, and this will not
> work with conditional formatting (any changes to the background color
> will be overridden by the conditional formatting). Is there anyway to
> do this in VBcode? Any help would be fantastic. Thanks!
>
> Rick
>

Hey Peter,

Could you explain how to create the three "FC's"? I know how to make a
condition such as 'less than a certain cell value' but I'm not sure how
to make a conditional format that relys on a checkbox and a cell value.
Thanks!

Rick


Peter T wrote:
> Hi Rick,
>
> If you're happy to use conditional formats, why not use 3 FC's
>
> FC1 checked and Today
> FC2 checked
> FC3 today
>
> where Checked refers to the Checkbox linked cell or some other value you
> change by code with the checkbox and today = cell's date (IsFormula).
>
> Regards,
> Peter T
>
>
> <RSummersJr@gmail.com> wrote in message
> news:1133535612.437374.54420@g49g2000cwa.googlegroups.com...
> > Hello,
> >
> > I'm trying to setup a macro that will automatically change the color of
> > a cell's background when the cell's date data equals TODAY(). I know I
> > can do this with conditional formatting, but I also need it to change
> > to another color once a checkbox has been checked, and this will not
> > work with conditional formatting (any changes to the background color
> > will be overridden by the conditional formatting). Is there anyway to
> > do this in VBcode? Any help would be fantastic. Thanks!
> >
> > Rick
> >

As he said, link the checkbox to a cell and then check the value of the cell
(indicating the status of the checkbox).

--
Regards,
Tom Ogilvy


<RSummersJr@gmail.com> wrote in message
news:1133540341.525101.205820@z14g2000cwz.googlegroups.com...
> Hey Peter,
>
> Could you explain how to create the three "FC's"? I know how to make a
> condition such as 'less than a certain cell value' but I'm not sure how
> to make a conditional format that relys on a checkbox and a cell value.
> Thanks!
>
> Rick
>
>
> Peter T wrote:
> > Hi Rick,
> >
> > If you're happy to use conditional formats, why not use 3 FC's
> >
> > FC1 checked and Today
> > FC2 checked
> > FC3 today
> >
> > where Checked refers to the Checkbox linked cell or some other value you
> > change by code with the checkbox and today = cell's date (IsFormula).
> >
> > Regards,
> > Peter T
> >
> >
> > <RSummersJr@gmail.com> wrote in message
> > news:1133535612.437374.54420@g49g2000cwa.googlegroups.com...
> > > Hello,
> > >
> > > I'm trying to setup a macro that will automatically change the color
of
> > > a cell's background when the cell's date data equals TODAY(). I know
I
> > > can do this with conditional formatting, but I also need it to change
> > > to another color once a checkbox has been checked, and this will not
> > > work with conditional formatting (any changes to the background color
> > > will be overridden by the conditional formatting). Is there anyway to
> > > do this in VBcode? Any help would be fantastic. Thanks!
> > >
> > > Rick
> > >
>

Rick, rather than use less than etc., you need to change the condition
dropdown to Formula Is and add a formula. Peter suggesting linking the
checkbox to a cell, so assuming it is a Forms toolbar checkbox, a formula
would be of the type

=AND(M1=1,A1=TODAY())

where M1 is the checkbox linked cell and A1 is the data cell.



--

HTH

RP
(remove nothere from the email address if mailing direct)


<RSummersJr@gmail.com> wrote in message
news:1133540341.525101.205820@z14g2000cwz.googlegroups.com...
> Hey Peter,
>
> Could you explain how to create the three "FC's"? I know how to make a
> condition such as 'less than a certain cell value' but I'm not sure how
> to make a conditional format that relys on a checkbox and a cell value.
> Thanks!
>
> Rick
>
>
> Peter T wrote:
> > Hi Rick,
> >
> > If you're happy to use conditional formats, why not use 3 FC's
> >
> > FC1 checked and Today
> > FC2 checked
> > FC3 today
> >
> > where Checked refers to the Checkbox linked cell or some other value you
> > change by code with the checkbox and today = cell's date (IsFormula).
> >
> > Regards,
> > Peter T
> >
> >
> > <RSummersJr@gmail.com> wrote in message
> > news:1133535612.437374.54420@g49g2000cwa.googlegroups.com...
> > > Hello,
> > >
> > > I'm trying to setup a macro that will automatically change the color
of
> > > a cell's background when the cell's date data equals TODAY(). I know
I
> > > can do this with conditional formatting, but I also need it to change
> > > to another color once a checkbox has been checked, and this will not
> > > work with conditional formatting (any changes to the background color
> > > will be overridden by the conditional formatting). Is there anyway to
> > > do this in VBcode? Any help would be fantastic. Thanks!
> > >
> > > Rick
> > >
>

Checkboxes from both Forms and Control toolbox menu's have a linked cell
property, albeit applied in very different ways. For this example set the
linked cell to $A$6.

Put some dates, mixed with some of today, in col-C with the first in C1.

With C1 selected, apply these CF's with appropriate formats

Formula is / =AND($A$6=TRUE,C1=TODAY())
Formula is / =$A$6=TRUE
Cell value is / equal to / =TODAY()

No relative addressing for C1 (remove $), so you can copy the cell and paste
special formats to other dates that also relate to the checkbox with linked
cell A6.

Regards,
Peter T

<RSummersJr@gmail.com> wrote in message
news:1133540341.525101.205820@z14g2000cwz.googlegroups.com...
> Hey Peter,
>
> Could you explain how to create the three "FC's"? I know how to make a
> condition such as 'less than a certain cell value' but I'm not sure how
> to make a conditional format that relys on a checkbox and a cell value.
> Thanks!
>
> Rick
>
>
> Peter T wrote:
> > Hi Rick,
> >
> > If you're happy to use conditional formats, why not use 3 FC's
> >
> > FC1 checked and Today
> > FC2 checked
> > FC3 today
> >
> > where Checked refers to the Checkbox linked cell or some other value you
> > change by code with the checkbox and today = cell's date (IsFormula).
> >
> > Regards,
> > Peter T
> >
> >
> > <RSummersJr@gmail.com> wrote in message
> > news:1133535612.437374.54420@g49g2000cwa.googlegroups.com...
> > > Hello,
> > >
> > > I'm trying to setup a macro that will automatically change the color
of
> > > a cell's background when the cell's date data equals TODAY(). I know
I
> > > can do this with conditional formatting, but I also need it to change
> > > to another color once a checkbox has been checked, and this will not
> > > work with conditional formatting (any changes to the background color
> > > will be overridden by the conditional formatting). Is there anyway to
> > > do this in VBcode? Any help would be fantastic. Thanks!
> > >
> > > Rick
> > >
>

typo,

> No relative addressing for C1

Relative addressing for C1 (obviously !)

(I didn't see earlier similar suggestions when I posted)

Peter T


"Peter T" <peter_t@discussions> wrote in message
news:u7av4Y29FHA.500@TK2MSFTNGP15.phx.gbl...
> Checkboxes from both Forms and Control toolbox menu's have a linked cell
> property, albeit applied in very different ways. For this example set the
> linked cell to $A$6.
>
> Put some dates, mixed with some of today, in col-C with the first in C1.
>
> With C1 selected, apply these CF's with appropriate formats
>
> Formula is / =AND($A$6=TRUE,C1=TODAY())
> Formula is / =$A$6=TRUE
> Cell value is / equal to / =TODAY()
>
> No relative addressing for C1 (remove $), so you can copy the cell and
paste
> special formats to other dates that also relate to the checkbox with
linked
> cell A6.
>
> Regards,
> Peter T
>
> <RSummersJr@gmail.com> wrote in message
> news:1133540341.525101.205820@z14g2000cwz.googlegroups.com...
> > Hey Peter,
> >
> > Could you explain how to create the three "FC's"? I know how to make a
> > condition such as 'less than a certain cell value' but I'm not sure how
> > to make a conditional format that relys on a checkbox and a cell value.
> > Thanks!
> >
> > Rick
> >
> >
> > Peter T wrote:
> > > Hi Rick,
> > >
> > > If you're happy to use conditional formats, why not use 3 FC's
> > >
> > > FC1 checked and Today
> > > FC2 checked
> > > FC3 today
> > >
> > > where Checked refers to the Checkbox linked cell or some other value
you
> > > change by code with the checkbox and today = cell's date (IsFormula).
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> > > <RSummersJr@gmail.com> wrote in message
> > > news:1133535612.437374.54420@g49g2000cwa.googlegroups.com...
> > > > Hello,
> > > >
> > > > I'm trying to setup a macro that will automatically change the color
> of
> > > > a cell's background when the cell's date data equals TODAY(). I
know
> I
> > > > can do this with conditional formatting, but I also need it to
change
> > > > to another color once a checkbox has been checked, and this will not
> > > > work with conditional formatting (any changes to the background
color
> > > > will be overridden by the conditional formatting). Is there anyway
to
> > > > do this in VBcode? Any help would be fantastic. Thanks!
> > > >
> > > > Rick
> > > >
> >
>
>