Share via

Cell Conditioning - How to make it go clear if cell is blank

Anonymous
2022-02-09T11:09:34+00:00

Hi

I have some conditional formatting on a cell making it go different colours depending on dates input in to other coloumns.

However, I would like the cell to go to a blank fill if there is nothing input in the cell (so A2 would go clear if nothing input to A2). But it isn't changing, it is just staying on the previous colour set from conditioning.

Can anyone help please?

Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-02-09T11:21:07+00:00

    Add a conditional formatting rule of type 'Format only cells that contain'.

    Select Call Value, equal to and ="".

    Click Format...

    Activate the Fill tab.

    Select No Color.

    OK your way out.

    9 people found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2022-02-09T15:56:01+00:00

    https://1drv.ms/x/s!AldNdi8CoTHPkT_1-3iZhT3dHamE?e=HYuOL3

    Thank you.

    There is an issue with the date formula, you compare if E3 is lower today, that is also true if the cell is empty, because an empty cell is calculated as 0 and therefore this condition is always true.

    The condition if C3 is empty is IMHO superfluous.

    And if you want to colors if there is no patient we have to apply a condition as Hans suggested, but we have to stop the formatting:

    Image

    As you see I've changed some formulas, so here is your file, take a look if it works as desired.

    https://www.dropbox.com/s/n2mwbdi08w25az0/631e1a89-fee2-4188-a82b-430574f54dcb.xlsx?dl=1

    Andreas.

    3 people found this answer helpful.
    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-02-09T11:55:36+00:00

    As I mentioned in my previous reply::

    0 comments
  4. Anonymous
    2022-02-09T11:41:58+00:00

    https://1drv.ms/x/s!AldNdi8CoTHPkT_1-3iZhT3dHamE?e=HYuOL3

    Thank you for responding. Please find above the spreadsheet.

    Thanks.

    Hannah

    0 comments
  5. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2022-02-09T11:23:53+00:00

    If I color A2 using a conditional formatting so if A2 contains something, why should A2 be colored if A2 is empty?

    The issue is anywhere inside your conditional formatting, show us your file.

    IMPORTANT: Zip your file!
    https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

    Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
    Click Upload in the top and choose your file.
    After uploading, right click the file and choose Share.
    Click Copy Link in the lower left edge (no need to enter an email).
    Copy the link and paste it here.

    Andreas.

    0 comments