Need help with conditional formatting [Solved/Closed]

Report
Posts
5
Registration date
Sunday May 21, 2017
Status
Member
Last seen
May 22, 2017
-
 x -
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
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
131
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
Posts
5
Registration date
Sunday May 21, 2017
Status
Member
Last seen
May 22, 2017

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... :-)
try replacing the ISNUMBER function with ISTEXT(D1).
LeslieAnne
Posts
5
Registration date
Sunday May 21, 2017
Status
Member
Last seen
May 22, 2017

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
Posts
5
Registration date
Sunday May 21, 2017
Status
Member
Last seen
May 22, 2017
> x
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))