Report

Need help with conditional formatting [Solved]

Ask a question LeslieAnne 5Posts Sunday May 21, 2017Registration date May 22, 2017 Last seen - Last answered on May 22, 2017 at 01:44 PM by 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
Helpful
+0
plus moins
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 5Posts 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... :-)
Reply
Leave a comment
Helpful
+0
plus moins
try replacing the ISNUMBER function with ISTEXT(D1).
LeslieAnne 5Posts 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
Reply
x- May 22, 2017 at 10:54 AM
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.
Reply
x- May 22, 2017 at 10:59 AM
If you have DATE or TEXT in the Cell try this:

=OR(ISTEXT(D1),ISNUMBER(D1))
Reply
LeslieAnne 5Posts Sunday May 21, 2017Registration date May 22, 2017 Last seen - 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. :-)
Reply
x- May 22, 2017 at 01:44 PM
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)) 
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!