CF to grey out the two unused options
 

The user has the option of entering a date in either of cells Y,Z or AA on
any given row. If Z is picked, for example, I would like the 2 unused cells
on the same row to be greyed out. If the date is then removed and put in AA,
then the CF would grey out the other 2 unused cells.
--
Traa Dy Liooar

Jock

CF to grey out the two unused options
 

On 30 Apr, 14:06, Jock wrote:
The user has the option of entering a date in either of cells Y,Z or AA on
any given row. If Z is picked, for example, I would like the 2 unused cells
on the same row to be greyed out. If the date is then removed and put in AA,
then the CF would grey out the other 2 unused cells.
--
Traa Dy Liooar

Jock

Hi Jock

In Cell Y1
Open the Conditional Formatting dialog box,
select "Formula Is",
type =Y1=""
Click Format
Click Pattern
Select the grey colour you want,
OK, OK,
Copy Y1 to the required cells (Pastespecial Formats if necessary to
avoid pasting over existing values)

Regards

Steve

CF to grey out the two unused options
 

Hi Scoops,
Your solution would work but grey out the entire columns except where data
was later entered into individual cells. I would like no formatting applied
until a date has been entered in one of the 3 cells.

Thanks though.
--
Traa Dy Liooar

Jock


"Scoops" wrote:

On 30 Apr, 14:06, Jock wrote:
The user has the option of entering a date in either of cells Y,Z or AA on
any given row. If Z is picked, for example, I would like the 2 unused cells
on the same row to be greyed out. If the date is then removed and put in AA,
then the CF would grey out the other 2 unused cells.
--
Traa Dy Liooar

Jock

Hi Jock

In Cell Y1
Open the Conditional Formatting dialog box,
select "Formula Is",
type =Y1=""
Click Format
Click Pattern
Select the grey colour you want,
OK, OK,
Copy Y1 to the required cells (Pastespecial Formats if necessary to
avoid pasting over existing values)

Regards

Steve

CF to grey out the two unused options
 

Jock wrote:
The user has the option of entering a date in either of cells Y,Z or AA on
any given row. If Z is picked, for example, I would like the 2 unused cells
on the same row to be greyed out. If the date is then removed and put in AA,
then the CF would grey out the other 2 unused cells.


=AND(Y1="",COUNT($Y1:$AA1)0)

CF to grey out the two unused options
 

Perfect, Thank you
--
Traa Dy Liooar

Jock


"Glenn" wrote:

Jock wrote:
The user has the option of entering a date in either of cells Y,Z or AA on
any given row. If Z is picked, for example, I would like the 2 unused cells
on the same row to be greyed out. If the date is then removed and put in AA,
then the CF would grey out the other 2 unused cells.


=AND(Y1="",COUNT($Y1:$AA1)0)

CF to grey out the two unused options
 

Jock wrote:
Perfect, Thank you


You are welcome.

You may want to consider a second CF of this:

=COUNT($A$1:$C$1)1

Format appropriately (bright yellow?) to highlight when more than one entry has
been made in the range.

CF to grey out the two unused options
 

Hi Glenn,
these work fine however, I have changed the functionality of the sheet so
that, rather than enter a date in Y:AA, the user will double click the cell
and a 'tick ("P" formatted as wingdings2) will appear.
Because this is done by code, the CF formula doesn't work (I presume).
Is there a way around this?

Thanks
--
Traa Dy Liooar

Jock


"Glenn" wrote:

Jock wrote:
Perfect, Thank you


You are welcome.

You may want to consider a second CF of this:

=COUNT($A$1:$C$1)1

Format appropriately (bright yellow?) to highlight when more than one entry has
been made in the range.

CF to grey out the two unused options
 

Jock wrote:
Hi Glenn,
these work fine however, I have changed the functionality of the sheet so
that, rather than enter a date in Y:AA, the user will double click the cell
and a 'tick ("P" formatted as wingdings2) will appear.
Because this is done by code, the CF formula doesn't work (I presume).
Is there a way around this?

Thanks

Try COUNTA().