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
RSummersJr@gmail.com wrote:
> 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
>
Hi Rick,
this command colors define the color of cell a1
If DateDiff("d", Range("a1").Value, Now()) = 0 Then
Range("a1").Interior.Color = RGB(0, 255, 0)
End If
Mike from Luxembourg
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
> > > >
> >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks