Forum Discussion

Zulekha's avatar
Zulekha
Copper Contributor
Jul 04, 2023

Excel Conditional Formatting

Is there a way for a conditional formatting rule to contain a general condition.

 

What I want to do is: if 1 cell contains text or a date another cell will state "Yes".

 

E.g if A1 = 04/07/2023 then A2 = Yes

If a cell contains any date then the other cell will say "Yes".

 

I'm using Excel 2016.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Zulekha 

    =A1<>"" 

    Write Yes in cell A2, font color white. Conditional formatting paste this formula into New Rule --> Use a formula to determine which cells to format.

    Font color automatic or black (depending on your preference).

    If in A1 date appears Yes not in A2 (becomes white), if date or not blank appears Yes (becomes black).

     

    See included sample file.

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

    Zulekha  wrote:  ``Is there a way for a conditional formatting rule``

     

    Note that formatting per se only affects how a cell appears.  It cannot alter the value of cell.

     

    So, if we use formatting to make the cell A2 appear to be "yes", IF(A2="yes",TRUE) elsewhere will still return FALSE(!).

     

    Also note that we cannot easily distinguish a date per se from any other numeric value.  Using the CELL function, we can determine if A1 has certain date formats.

     

    That said, IMHO, it would be better to enter the following formula into A2:

     

    =IF(A1="", "", "yes")

     

    Format as General.  No Conditional Formatting.

     

    But if you insist on using formatting, enter zero into A2 and format as Custom ;;""

     

    Thus, A2 normally appears blank, even though its value is zero.

     

    Then with Conditional Formatting, enter the following rule:

     

    Formula:  =A1<>""

    Format: Custom "yes"

     

    See the attached "xlsx" file.

     

    -----

    EDIT....  As noted above, we cannot easily determine if a cell format displays "yes".

     

    But with VBA, we use the range.Text property to make that distinction.

     

    This is demonstrated in row 3 of the "xlsm" file attached below.

     

    The VBA function is:

     

    Function displayYes(r As Range) As Boolean
    displayYes = (r.Text = "yes")
    End Function

Share