Excel Conditional Formatting

Copper Contributor

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.

2 Replies



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.

@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