Conditional Format: Change Text if Other Cell Not Blank

[PostTIT]

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.

 

[PostTIT]

So further to yesterday's great help that you provided I was wondering if you could help me take it another step?

Through minor edits to my spreadsheet the piece of code that you provided now looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)


'   Check to see if single value in column J updated
    If Target.Count = 1 And Target.Column = 10 Then
'       If column K is populated and column K equals "Due", change to "Complete"
        If Target <> "" And Target.Offset(0, 1) = "Due" Then
            Target.Offset(0, 1) = "Complete"
        End If
    End If
    
'   Check to see if single value in column J updated
    If Target.Count = 1 And Target.Column = 10 Then
'       If column K is populated and column K equals "Complete", change to "Due"
        If Target = "" And Target.Offset(0, 1) = "Complete" Then
            Target.Offset(0, 1) = "Due"
        End If
    End If
End Sub

What I'm wanting now is for the same cell to change text based purely on the value in a different Target cell ('Target.Column = 9' or 'column I'). And if value in the cell (the value will always be a date) is before today's date, then I want the cell with text to change from "due" to "overdue". I think I could do this myself, but I want to be sure. My train of thought creates the following (which I know doesn't work but it shows the idea, I think):

'   Check to see if single value in column J updated
    If Target.Count = 1 And Target.Column = 10 Then
'       If column K is populated and column J equals "Complete", change to "Due"
        If Target = "" And Target.Offset(0, -1) > TODAY() Then
            Target.Offset(0, 1) = "Overdue"
        End If
    End If

I also wanted to clarify whether the above bit of code will supersede this new bit I'm needing/wanting/asking for so that if the 'completion condition' is met then it stays that way, regardless of if the date in column I is before today's date.

It must be possible, almost everything is with Excel.

 

[PostTIT]

Effectively I want this formula (which would be in every cell of Table14[Status] (Column K):

=IF(Table14[Due Date]<today()&table14[completion date]="" ,"outstanding",if(table14[completion="" <=""> "","Complete","Due"))

In macro form, so that it goes through all the rows of the table...

This part of the table currently looks like this:
If today's date is the 20/717
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Due Date[/TD]
[TD]Completion Date[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]24/7/17[/TD]
[TD][/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD]24/7/17[/TD]
[TD][/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD]19/7/17[/TD]
[TD]19/7/17[/TD]
[TD]Complete[/TD]
[/TR]
[TR]
[TD]19/7/17[/TD]
[TD][/TD]
[TD]Due[/TD]
[/TR]
</tbody>[/TABLE]










And then I want the code to change that last row (as today's date has now surpassed it) to change the value in column K to "Outstanding"

Anyone able to help? I made a pretty table after all...</today()&table14[completion>

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

'   Check to see which column was just updated
    Select Case Target.Column
        Case 9
'           If column I
            If Target > 0 And Target < Date And Target.Offset(0, 2) = "Due" Then
                Target.Offset(0, 2) = "Overdue"
            End If
        Case 10
'           If column J
            If Target <> "" And Target.Offset(0, 1) = "Due" Then
                Target.Offset(0, 1) = "Complete"
            Else
'           If column J is populated and column K equals "Complete", change to "Due"
                If Target = "" And Target.Offset(0, 1) = "Complete" Then
                    Target.Offset(0, 1) = "Due"
                End If
             End If
    End Select

End Sub

Note that you didn't make any mention about changing from "Overdue" to "Complete.

 

[PostTIT]

Thanks man, works a treat!