CF to grey out the two unused options

  • Thread starter Thread starter Jock
  • Start date Start date
J

Jock

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.
 
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.
Click to expand...

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
 
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.
 
Jock said:
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.
Click to expand...


=AND(Y1="",COUNT($Y1:$AA1)>0)
 
Jock said:
Perfect, Thank you
Click to expand...


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.
 
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
 
Jock said:
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
Click to expand...

Try COUNTA().
 
Back
Top