If / Vlookup statement

[RichJW]

Hi,

Ref Excel 2016.
I've been trying to use an IF statement to reference the data in column C, which in turn will change the text in column E.

So, if column C shows the word "Closed", then the text in column E needs to then display the word "Blue".
The difficulty I'm finding in using an IF or Vlookup formula is the ending, as I want all other data in column E to remain. With my formula it is deleting whatever remains in the cell in column E, as not everything in column C equals "Closed". I only wish for the data in column E to change if "Closed" is in column C - everything else in column E needs to stay as it is. There is a changing amount of rows each time.

I'm missing something simple, but cannot quite get it.
If I could add it as a macro, that would be a bonus.

Thanks,
Rich

 

[edgarsrom]

Hello RichJW,

I can suggest to use loop, which will check column C for word "Closed" and input word "Blue" into column E:

Sub mysub()
Dim rng as Range
Dim cell as Range

Set rng = Activesheet.range("C2:C300") 'Amend range as required for your sheet

For Each cell in rng
If cell.value = "Closed" then
Cell.offset(0,2).value = "Blue"
End If
Next cell
End Sub

See if it helps.

 

[Yongle]

Hi Rich
Your explanation is confusing me

Is column E plain text or is it a value returned by a formula?
- If it is plain text then you must put your formula in another column and refer to both C and E (or use VBA to replace the text)
- If the value is returned by formula please post the formula

 

[RichJW]

Hi edgarsrom,

That is wonderful and does exactly what I required.
Thanks so much for your help.

Yongle, column E is plain text, sorry if my explanation wasn't good enough - it was confusing me too

Cheers all,
Rich