Conditional Format: Change Text if Other Cell Not Blank

P

PostTIT

Board Regular
Joined
Aug 22, 2016
Messages
94
As the title says I'm wanting conditional formatting to be in use.
Column K:K contains cells that are to be manually changed from blank to not-blank.
When the cell is not blank I want to cell beside it, in Column L:L, to change text from "Due" to "Complete".

Is this possible? I've managed to get it to change format of the cell via: =NOT(ISBLANK(K1) but I need to change the text within the cell, not the formatting, as other cells/formulae are reliant on it changing.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Changing values is not Conditional Formatting. Conditional Formatting has to do with just that, the format of cells (color, font, shading, underlining, etc).

It sounds like what you want is a formula, something like the following in cell L1 (and copied down):
Code: 
=IF(K1="","Due","Complete")
 
Upvote 0
You cannot use CF to change the text in a cell.
A formula like =IF(K1<>"","complete","due")
Pull down as needed
 
Upvote 0
Cells cannot change the values of other cells, you need VBA for this.
You can however set the value of a cell based on another cell.
So you could have a formula in L1
=IF(K1<>"","Complete","Due")
 
Upvote 0
Not sure you can do that with Conditional Formatting but why not use an If statement?

Copy this formula into Cell L2 and then copy down for all rows you want to evaluate: =IF(ISBLANK(K2),"Due","Complete")
 
Upvote 0
Be aware that ISBLANK only works on an empty cell. If it contains the "" null string, results will be incorrect.
This does work, however with COUNTBLANK
 
Upvote 0
Thanks all. I was aware of using the IF statement and having it work that way, however I've basically made this not an option with the way I've set up my spreadsheet...

I have two sheets, the sheet that I'm concerned with (sheet two) has a table on it with lots of data and the IF statement would work great if it wasn't for how the data gets added to the table:

The data is entered on sheet one in a one-row version of the same table, the user clicks a button and a macro copies the data to the table on sheet 2 in the newly inserted first row (so that it's newest item first). The table on sheet two can then be changed manually when needed by the user, and it's at that point - when the user puts in a completed date in the relevant cell - I was wanting the cells' text to change from "Due" to "Complete". So the cell already has the word "Due" in it and wouldn't be able to contain the IF statement...

Any way around this? Or does it have to be manually done?
 
Upvote 0
For this to happen automatically will require VBA.
Right-click on the sheet tab name at the bottom of your sheet, select View Code, and paste this code into the resulting code window:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if single value in column K updated
    If Target.Count = 1 And Target.Column = 11 Then
'       If column K is populated and column L equals "Due", change to "Complete"
        If Target <> "" And Target.Offset(0, 1) = "Due" Then
            Target.Offset(0, 1) = "Complete"
        End If
    End If
    
End Sub
This should do what you want.
 
Upvote 0
You're the man! Thanks.

Funny how such a small amount of code takes ages to learn. Practice here I come.
 
Upvote 0
You are welcome!

The key is that it is Event Procedure VBA code. Event Procedures are VBA code that run automatically upon some "event" happening (like the changing of a cell).
There is a good write-up on Event Procedures here: Events In Excel VBA
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Conditional Formatting with Self-Reference to ISBLANK
Replies
6
Views
186
Fluff
Fluff
N
  • Solved
Conditional format when another cell is blank (But is it??)
Replies
3
Views
144
Fluff
Fluff
W
  • Solved
Conditional formatting - formula for blank or specific text
Replies
3
Views
278
Joe4
Joe4
D
  • Question
Multiple conditions
Replies
8
Views
149
awoohaw
awoohaw
B
  • Question
Conditional Formatting for multiple cells based on other cell value
Replies
4
Views
245
Benong
B

Forum statistics

Threads
1,214,614
Messages
6,120,530
Members
448,969
Latest member
mirek8991

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top