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 1, 2019

You cannot use Conditional Formatting, as Conditional Formatting can only change the format of the data being returned in that cell. It cannot change the value, just how the value is shown (color, bolding, decimal places, date format, etc).

To do what you require will either require the use of another cell, or VBA.

still learning · Aug 1, 2019

Hi Joe4
Thanks for the reply
What would a macro do?
At worksheet open, Look in column B for all the texts "pending" and if the date =today(), it would change then to "paid'. Then do a
Selection.Value = Selection.Value for each one?

Could you start me off?

mike

Joe4 · Aug 2, 2019

Try putting this code in the "ThisWorkbook" module:

Code:

Private Sub Workbook_Open()

    Dim lr As Long
    Dim r As Long

'   Select sheet to run this code against
    Sheets("Sheet1").Activate
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows
    For r = 1 To lr
'       See if date in column A is today and column B is pending
        If (Cells(r, "A") = Date) And (LCase(Cells(r, "B")) = "pending") Then
            Cells(r, "B") = "paid"
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

Note that you may need to update the sheet name referenced in the code if the sheet you want to run it on is not "Sheet1".

As long as you have macros enabled, when you open the file, it will run automatically.
Note you may want to tweak the condition, if the file will not necessarily be opened every day, so some days don't get missed (i.e. check for dates less than or equal to Date).

still learning · Aug 2, 2019

I already have a private sub routine in the sheet module

Code:

Private Sub Worksheet_activate()
Range("A1").Select
Range("A5").Select
ActiveWindow.FreezePanes = True
Selection.End(xlDown).Select
ActiveWindow.ScrollRow = ActiveCell.Row - 10
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Do I keep both since one is only when the book opens and the other is when the sheet is becomes active (like when i switch to a different sheet in the same books and back

Also how would I change your code to start at particular row. I only need it to look at from today on. today starts at A1170
I used A1 and B1 to make it easier to understand.

I won't be able to try anything for a few days, either

mike

JonXL · Aug 4, 2019

The closest I can see to using Conditional Formatting is to get some text added to the cell, but not replacing what is there.

For example, in B1 you can add a formula for the Conditional Formatting of:

Code:

=AND(B1="PENDING",A1>=TODAY())

For the format, set it to something like this (Number>Custom>Type

:

Code:

"PAID (WAS "@")"

This will switch the text from "PENDING" to "PAID (WAS PENDING)" when the values in A1 and B1 match your criteria.

If the value in B1 were a number, you could replace it in full instead of just adding to it. (Unless someone knows something I don't! - highly likely).

ABE: I should point out that this does not change the VALUE of B1, so if you need to rely on the presence/absence of the word 'PAID' for some other task, this will not work - but for that you could possibly just use the same formula that is used in the conditional formatting for determining the PAID/PENDING status.

Rick Rothstein · Aug 4, 2019

JonXL said:
If the value in B1 were a number, you could replace it in full instead of just adding to it. (Unless someone knows something I don't! - highly likely).

The value in cell B1 does not have to be a number in order to replace it. With the set up you outlined, try this Custom Format Type pattern...

;;;"Paid"

JonXL · Aug 4, 2019

Rick Rothstein said:
The value in cell B1 does not have to be a number in order to replace it. With the set up you outlined, try this Custom Format Type pattern...

;;;"Paid"

Thank you! I've tried to do this in the past with no luck - I am sure I will need that formatting again. Very useful information.

Joe4 · Aug 5, 2019

I already have a private sub routine in the sheet module

Do I keep both since one is only when the book opens and the other is when the sheet is becomes active (like when i switch to a different sheet in the same books and back

Thre are totally different event procedures that go in totally different places. The one you have goes in the proper Sheet module. The one I gave you must go in the "ThisWorkbook" module. It will not work if you place it in the Sheet module.

Also how would I change your code to start at particular row. I only need it to look at from today on. today starts at A1170
I used A1 and B1 to make it easier to understand.

I won't be able to try anything for a few days, either
Note that you can change the code to start at any row that you like. The loop starts at one, but you can change that:

Code:

    For r = [COLOR=#ff0000]1[/COLOR] To lr

Just change the "1" to whatever row number you want to start at.

still learning · Aug 7, 2019

HiJoe 4
I put your macro in This Work instead of the sheet module and it works perfect. I was able to have it start wher I wanted it after I changed the 1
Thank you

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
I know I did something wrong

thanks for replying

Hi JonXL,
Also,Thank you

mike

conditional format to change text

still learning

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

still learning

Well-known Member

Joe4

MrExcel MVP, Junior Admin

still learning

Well-known Member

JonXL

Well-known Member

Rick Rothstein

MrExcel MVP

JonXL

Well-known Member

Joe4

MrExcel MVP, Junior Admin

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

conditional format to change text

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP, Junior Admin

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