conditional format to change text

still learning · Aug 1, 2019

Hi

I want to use conditional format to change the text in B1 if it contains “pending” and A1 is >=today() to “paid”.
If the cell contains any other text, I don’t want it to change
If the cell is blank, I want it to stay blank

I then will copy it down as needed

I can’t use an If statement in B1 because I get an error message (circular reference) >>one of the conditions is base on the cell that the If statement is in.

I only know about Conditional format and If statements.
mike

Joe4 · Aug 7, 2019

You are welcome.
Glad we could help.

Rick Rothstein · Aug 7, 2019

still learning said:
Hi Rick,
I put your JonXL's code in the conditional format box and then I changed the Custom Format Type pattern ;;;"paid"
it changed all the text in B to paid

Which Custom Format did you set... the one in the cell directly or the one inside the Conditional Formatting dialog?

still learning · Aug 7, 2019

Hey Rick
Yep. I put it in the cell directly, I never saw the one in conditional format
I then:
I clicked on B1 and in conditional format under ...use formula to...I typed in

Code:

=and(B1="pending",A1=>today())

then I went into the custom form box in conditional format and put in numbers

HTML:

;;;"paid"

I changed the date to before today then today then after today and nothing happened.

I've got Joe4's code working, but as my name suggests, I want to learn

mike

Rick Rothstein · Aug 7, 2019

still learning said:
Hey Rick
Yep. I put it in the cell directly, I never saw the one in conditional format
I then:
I clicked on B1 and in conditional format under ...use formula to...I typed in

Code:

=and(B1="pending",A1=>today())

then I went into the custom form box in conditional format and put in numbers

HTML:

;;;"paid"

I changed the date to before today then today then after today and nothing happened.

I just tested it again and what I posted works fine for me. The only thing I can see wrong with what you did is in your formula... you wrote the condition for the date as => which is incorrect, it should be >=; however, if you actually did that in Conditional Formatting dialog, it would have flagged the formula as being invalid and forced you to fix it or abandon the attempt to set a Conditional Format. I think you need to check the formula you used in the Conditional Formatting dialog making sure the cell references are for the correct cells. As I said, what I posted works so you are doing something incorrectly, but without my seeing your workbook, I cannot tell what. If you verify the formula is correct, or if you change it, and you still cannot get it to work, I would suggest uploading your workbook (with sensitive info removed) to DropBox and posting the sharing link they give you for here so we can look at your actual data and exactly what you typed into the Conditional Formatting dialog.

still learning · Aug 7, 2019

I made a sample sheet and filled A2 to A7 with dates and B2 to B7 with pending
I highlighted B2 to B7 and put in the formula in the conditional format box for B1 and A1.
It took awhile to figure to that the was referring to A1 when it was empty

Maybe I shouldn't have had the 3 beers

Thank you for helping

mike

conditional format to change text

still learning

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Excel Facts

Rick Rothstein

MrExcel MVP

still learning

Well-known Member

Rick Rothstein

MrExcel MVP

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

conditional format to change text

still learning

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Excel Facts

Rick Rothstein

MrExcel MVP

still learning

Well-known Member

Rick Rothstein

MrExcel MVP

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock