Excel Conditional Formatting

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