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

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


wrote in message
ups.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


wrote in message
ups.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


wrote in message
oups.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


wrote in message
ups.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)


wrote in message
oups.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


wrote in message
ups.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

wrote in message
oups.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


wrote in message
ups.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
...
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

wrote in message
oups.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


wrote in message
ups.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