Need help with conditional formatting [Solved/Closed]

LeslieAnne 5 Posts Sunday May 21, 2017Registration date May 22, 2017 Last seen - May 22, 2017 at 07:45 AM - Latest reply:  x
- May 22, 2017 at 01:44 PM
Good morning,
I am trying to change the color of a cell(A) if another cell (D) contains text. The text will contain both a date and other text.
Can anyone help me figure out how to do this? I was up for hours trying to figure it out by myself......I am most grateful for this forum!
Thank you, so much, in advance.
LeslieAnne
See more 

8 replies

Mazzaropi 1831 Posts Monday August 16, 2010Registration dateContributorStatus May 21, 2018 Last seen - May 22, 2017 at 09:16 AM
0
Thank you
LeslieAnne, Good morning.

Select cell A1 to apply the CF.

Menu Conditional Format
Using Formula
=ISNUMBER(SEARCH("Desired Text",D1))

Select colour format as desired
OK

Please, tell us if it worked as you desire.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
LeslieAnne 5 Posts Sunday May 21, 2017Registration date May 22, 2017 Last seen - May 22, 2017 at 10:20 AM
Thank you, so much. You were so quick!
But, it still doesn't work. If I change the text to be only a date, and remove "desired text" and replace it with "date" will it read any date and change the format color of the original cell?
I really appreciate not feeling alone in this... :-)
0
Thank you
try replacing the ISNUMBER function with ISTEXT(D1).
LeslieAnne 5 Posts Sunday May 21, 2017Registration date May 22, 2017 Last seen - May 22, 2017 at 10:47 AM
Hi! Thanks for helping. But it still doesn't work. This is what the rule looks like now:
=ISTEXT(SEARCH("Desired Text",E8))
and the original cell (A8) remains unchanged.
If I want it read just a date, would I change it to:ISTEXT(SEARCH("DATEt",E8)) ?

I really, really appreciate your help! LeslieAnne
you just need to use ISTEXT(D1).
This checks if the cell contains text. DATE and TEXT=TEXT
If you have DATE or TEXT then another solution is needed as ISTEXT does not detect Dates.
If you have DATE or TEXT in the Cell try this:

=OR(ISTEXT(D1),ISNUMBER(D1))
LeslieAnne 5 Posts Sunday May 21, 2017Registration date May 22, 2017 Last seen > x - May 22, 2017 at 11:25 AM
Wow! We're finally getting something! Thank you.
Now, the last thing is that I want each row to read it's own D column instead of being stuck on D1.
So A3 will read E3, A8 will read E8, etc. They all seem to be reading D1.
What would I change for that? I tried copying down the column and nothing moved...
My formula now reads: =OR(ISTEXT(D1),ISNUMBER(D1))

Thank you, again, over and over and over. :-)
The cell references should be handled by the conditional formatting.
You dont need to copy anything down.

Select the range of cells that you want to format, choose Conditonal Formatting from the menu, Choose formula, enter the formula and pick the color.

I should say that if you do have a Number in the cell, say 123, that this will also be colored. If this is the case and is not desirable then you will have to make your own ISDATE function in VBA.

create a module and paste this in.

Function myIsDate(rng As Range)
myIsDate = IsDate(rng)
End Function


Then use it like this:

=OR(ISTEXT(D1),myIsDate(D1))